The large formula is an excellent tool that I haven’t really used that much until just recently. The beauty about it is that you can search through a large set of data, and identify the first, second, third, fourth …..Nth largest number/value in any given data set for a column/field. When I started to really think about this, I realized that this could be a great tool (perhaps even the secret ingredient) to building detailed dashboards showing the top 3, 5 or 10 records for pretty much any data set that you could think of. In this post, you are going to use the large formula first to identify the top 3 records of your entire data set, then you’ll use the index formula in combination with the match formula to identify the corresponding values that will make up your entire record. We will then have a look at how you can find the top 3 records based on a certain criteria within your data set. Here you will also use the index match formula using multiple criteria to get your corresponding values to complete each of the top 3 records.
Here is what your final product for the dashboard preparation worksheet will look like.
Let’s have a look at how you are going to do this!
Download the Workbook For Free Before We Begin
The Excel file is indeed free. When you click the ‘GET IT FREE’ button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you’ll need is your email address!
Using the Large Formula to Identify the Top 3 Records in Your Data Set
This part of the process is pretty simple. You select the column/field that you want to identify the top 3 records for and then you’ll begin your formula in your newly created top 3 table against the appropriate column. In this example, you are going to sort based on the extended value of your inventory table.
The Large Formula Syntax
=Large(array, k)
Array: The array/range that you want to find the Kth largest value for.
K: The k identifies which largest number you would like to display ( for example, the 1st, 2nd, 3rd, 4th and so forth largest number in your data set).
How to Use the Large Formula to Find the Largest Value in a Data Set
Now that you’ve reviewed the formula, all you need to do is plugin the formula into your calculations range. Here we are going to be looking to find the top 3 (K=1, K=2, K=3) highest extended values based on all of our inventory records.
Step 1:
Select the first cell of our extended value column/field on the calculations tab and enter in the following formula as shown below.
Step 2:
Begin the large formula, open the bracket and then select the range from your inventory data table that you’d like to return the highest extended value from.
Step 3:
After you’ve selected your range, type the value 1. This literally means show me the highest number in the data set that I just selected. Close the bracket out.
Step 4:
Hit enter. You’ll notice now that we’ve added the highest extended value on the calculations tab.
Copying Your Formula to the Other Cells in Your Table
This part is also very easy. Simply copy and paste your formula to the other cells in the extended value column of your “top 3 highest inventory valuation all items” data set. Once you’ve done this, the only thing you’ll need to do is update the K value of each formula. The value in the second row will of course be a 2 and then in the 3rd row it will be a 3.
Using the Large Formula With the If formula to Identify the Top 3 Records Of a Specific Product Category
The large formula can work with the if formula to help bring in another level of detail into our dashboard preparation. In the example below I’ll show you how you can use the if formula to find the highest/largest value of a specific data set based on any given criteria. In the case that follows, you’ll be looking at using the product category as a criteria. Here we will show the top 3 largest/highest inventory values for the product category selected by the user.
How to Use the IF formula within the Large Formula
The if formula is used here to find the largest values (or second, third, fourth and so on) based on a given criteria. The IF formula will look at the product category selected by the user in the data validation drop down list, and then will review and look at all of the records within the product category field in our table (see formula highlighted below in yellow). If it finds a match, it will look at the extended value associated with that record. If it is false, it will display or do nothing. Once we close out our IF formula, we will then pick which largest value we want to display (the large formula section is highlighted in blue below). In our case we want to display the 1st, 2nd and 3rd largest values for the product category selected by the user into three different cells. I’ve also included an IFERROR statement which says that if we find no records for that product category, display nothing (IFERROR formula is highlighted in orange below). Finally, this formula is an array which means we will need to type CTLR + SHIFT + ENTER once we’ve finalized inputting our formula otherwise the formula will not work (you can tell a formula is an array when you see the squiggly brackets at either end of the formula). You can have a look at how the formula is structured in the screen shot below.
Repeating and Copying your Formula Down to Other Cells
The next step is to copy your formula in the screen shot above and then changing the large formula (k) value to be 2 and then 3 respectively (don’t forget to hit CTRL + SHIFT + ENTER when you’re done). This will of course then show the 2nd and 3rd largest value for the selected product category for the extended value.
Using the Index Match Formula (With and Without Multiple Criteria) to Fill In The Remainder Of Your Summarized Data Set
Using the Index Match formula is the final step in creating and developing the perfect dashboard preparation worksheet. The formula is used to find the corresponding values for the largest extended value record that was found. I am not going to go into any detail on how to use the index match formula here, rather I’ll provide a link to a post showing you how to use this formula. Again, you can also download the workbook above to get a better idea of how to do this.
Learn How to Use the Multiple Criteria Index Match Formula Here
The Final Product and Why the Large Formula is the Secret Ingredient to your Dashboard Preparation Worksheet
I want to start off here by saying that typically the drop down validation list would be displayed on the dashboard page. Because I was showing you how you could apply the large formula to your dashboard preparation sheet, I included the validation list at this level instead. You may still be wondering why the large formula is the secret ingredient to our dashboard preparation sheet. Honestly it’s pretty simple. You can use the large formula (or the small formula) to summarize large sets of data into the finer details of the information. This means we can find the top records within a data set (based on whatever criteria we want) and have them displayed in a nice neat worksheet. Are you as excited as I am about this?
This means that we can decide exactly what records we want to see based on the top or bottom values of a given field within our data set. Think about all of the possibilities here:
- Top sales records for a region (using the region criteria).
- Bottom and slowest moving inventory (by product category or for all products) – substitute the small formula for the large formula here.
- Top 10 highest costs for any given month or year.
- Top 10 highest inventory items.
Okay, so what if I’m tired and I don’t want to write out the million different scenarios. You can literally use this for any industry where you want to display detailed records that are in the top or bottom range to identify areas of weakness or success.
All that being said, this is what you’re final product will look like once we’ve applied the large formula with the IF formula as well as using the INDEX MATCH formula to fill out the rest of our tables.
Final Thoughts and Summary
Using the LARGE formula, you can build some pretty sophisticated dashboard preparation worksheets that will display all of the summarized records and values that you want to see in a final dashboard. I’m sure that you can think of several ways that this may be useful to you, so I hope that you’ll leave a comment letting me know how you plan on using it.
Let’s summarize what we covered in this article:
◊ Use the LARGE formula to find the top records in a given data set based on a specific field (extended value of inventory).
◊ You can use the LARGE formula in combination with the IF formula to find the top records for a specific field based on a corresponding criteria (i.e. top 3 records for product category 1).
◊ Once you’ve got your top records and results, you can then use the INDEX MATCH formula to find the corresponding values of all of the other fields that match that record. This will then allow use to display a final dashboard preparation table worskheet.
That pretty much summarizes in a nutshell what you learned in this article today. Again, please feel free to leave some comments on other ways you think you would find these formulas useful as well as comments on potentially better ways of applying the same logic.
If you enjoyed reading this article please be sure to share!
Slow and steady wins the races