Finding 2nd Largest Item in a List that meets a Criteria [Excel Array Formulas]
Excel Array Formulas have subtle beauty and raw power. Today I want to share how you can use Excel Array Formulas to findout the 2nd largest sale amount corresponding to a particular product.
Lets say you have sales data like this:
Now, we all know that, to get 2nd largest sale amount, we can use the LARGE() formula. Like this: =LARGE(RANGE,2)
But, how do we know what is the 2nd largest sale amount for the product “bricks”?
Now, that is tricky? Isn’t it?
This is when an array formula can help you.
First the formula:
Type below formula in a cell
=LARGE(IF(PRODUCTS_RANGE="Bricks",SALES_RANGE,""),2)
and press CTRL+SHIFT+ENTER.
Now, the explanation:
Instead of directly giving the SALES_RANGE to LARGE formula, we are asking excel to include empty spaces “” wherever product <> “Bricks” thru the IF() formula inside LARGE().
Now, when you press CTRL+SHIFT+Enter, excel treats the formula as array formula and processes the lists of values inside nicely.
Download Excel File with Array Formula Examples:
I have put together 4 simple examples to help you understand the power of array formulas. Please download the file from here.
How do you use array formulas?
While array formulas are powerful, they are also difficult to write and explain. I try to solve problems using nonarray formula methods as much as possible. But in some cases, like above, I end up writing array formulas.
What about you? Do you use them? How differently would you solve the above problem? Please share using comments.
More examples on array formulas:
 Average of Top 5 Values – Array Formula in Excel
 Sumif with multiple conditions
 Array formula to check if a number is prime
 Using array formulas to find if a list has duplicate items
 
 

Leave a Reply
Bad News & VLOOKUP Contest  Insert Currency Symbols & Other Special Characters in Excel [Quick Tip] 
15 Responses to “Finding 2nd Largest Item in a List that meets a Criteria [Excel Array Formulas]”
Thanks Chanddoo…. never thought of this use of the formula
Define cAmt = IF(PRODUCTS_RANGE=”Bricks”,SALES_RANGE)
=Large(cAmt,2) no need of CSE
@Sam.. interesting idea. I did not know that named ranges would eliminate the need for CSE.
@Chandoo –
.
All named formulas (named ranges included) are ARRAY formulas.
.
I delve into this in great detail in my new Excel Hero Academy, which starts in a few days!
.
http://www.excelhero.com/blog/2010/09/excelheroacademy—interested.html
.
Regards,
Daniel Ferry
excelhero.com
Hello Chandoo:
For example, if the second value is same (t0001100, t0003100) for 2 Transaction IDs, based on this large formula, i will get the first transaction id only 2nd one it will not come.
In that what should do??? pl suggest.
~Rajesh~
a couple of things:
>what the heck does “CSE” stand for… maybe “Control/Shift/Enter”?
>every month or so, Chandoo comes up with another simple, sweet, elegant “communication” of Excel features. This is yet another 15 carrot diamond in a long string of diamonds. Thank you…
>i use indirect references a lot (as opposed to named ranges). my indirect reference is to a cell with a formula that extracts the sheet name and the cell range of the array through simple beg and end cell references. for me this works better than named references as the named range definition is once removed from view and named ranges are harder to housekeep (when you need hundreds of them).
>the elegant formula that Chandoo has presented works for just about any formula i have tried it on. this must be the underlying formula structure for COUNTIFS, SUMIFS and AVERAGEIFS. wow!
>this formula works even if the filters (“blocks”, “bricks”) are in independent reference cells which makes it very versatile for active dashboards and graphs.
>not only does Chandoo’s formula work for SMALL and LARGE but also MIN, MAX, MEDIAN, STDEV, CORREL, QUARTILE, VAR, SUMSQ, etc. etc. again wow!
>i have not been able to get this concept to work with LINEST, anyone know why?
>i have not tried it yet with the matrix or financial formulas.
bill
@Bill
CSE is as you suggested Ctrl Shift Enter
i am having trouble getting the following to work…
if i add a final column to the example dataset called PAID_RANGE and add either “paid” or “unpaid” to each row in that column then why won’t this formula work (for finding the largest block sale that has been paid):
{=LARGE(IF(AND((PRODUCTS_RANGE=”Bricks”,SALES_RANGE,”"),(PAID_RANGE=”paid”,SALES_RANGE,”"))),1)}
@Bill: You can add additional conditions like this:
{=LARGE(IF(AND(PRODUCTS_RANGE=”Bricks”,PAID_RANGE=”paid”),SALES_RANGE,””),1)}
Both conditions go inside the AND() formula.
To solve this an avoid an array (CSE) formula I would use SUMPRODUCT:
=SUMPRODUCT(LARGE((B2:B11=E2)*(C2:C11),2))
@Chandoo: with regard to the example with an added criteria (“paid” vs “unpaid”), I tried rearranging the terms as you have suggested…
{=LARGE(IF(AND(PRODUCTS_RANGE=”Bricks”,PAID_RANGE=”paid”),SALES_RANGE,””),1)}
This works, sort of. If you examine each element of the array created within the formula, you find that when both filters are TRUE than you get the value from the SALES_RANGE array (this is as expected) but if one or more of the filters are FALSE then you get an error (#VALUE) assignment instead of the empty cell assignment (“”). So the result of this formula is always #VALUE even when entered in CSE fashion.
This is a problem since almost all of the formulas (SMALL, LARGE, MIN, MAX, MEDIAN, STDEV, CORREL, QUARTILE, VAR, SUMSQ, etc.) work fine with an empty cell “” in the array but not with a #VALUE in the array. Also, note that for many of these listed formulas, zero can be a valid value so, I think, that the multiplicative (and additive approach for OR) would not work because zeros would be evaluated as array elements (for these example formulas).
bill
@Bill:
{=LARGE(IF((PRODUCTS_RANGE=”Bricks”)*(PAID_RANGE=”paid”)*(ISTEXT(PAID_RANGE)),SALES_RANGE,FALSE),2)}
I haven’t really tested it, but might work in that instance? CSE entered
@Lee,
>your suggestion works. you do have to use CSE. i do not get exactly why it works (yet), but it does. i tried it for two conditions. each element in the formula array evaluated to the cell value in SALES_RANGE or to a blank cell “” when both filer conditions were not met. >note i changed the FALSE argument to “” although it seems that the FALSE word gets treated the same as an empty cell for the LARGE, MIN, MAX, MEDIAN types of formulas).
>i use the following formula to view the array value by value (this is the IF statement with an INDEX formula substituted for the LARGE formula overwrap):
{=INDEX(IF((PRODUCTS_RANGE=”Bricks”)*(PAID_RANGE=”paid”)*
(ISTEXT(PAID_RANGE)),SALES_RANGE,”"),n,1)}
where n=row number in array
thank you!
bill
Thank you, this is very useful. I am curious if there’s an efficient way using just formulae (no VBA/macros/addins) to return the transaction ID instead of the amount?
To expand on the example in the article, imagine the list of transactions is ~200,000 rows. I want to list the top 10 transactions based on quantity of Bricks sold. I don’t want the formula to return the #1 largest quantity of bricks sold, I want it to return the transaction ID that corresponds to the #1 quantity of bricks sold.
Obviously I can put the source data into a table and use filtering & sorting to see this, but in addition to Bricks and Blocks I also sell 30 other things, so it’s a big time saver for me if Excel will list my top 10 transaction IDs for each item sold rather than me having to work the filters and extract the data myself.
I hope that makes sense.
You can try =large(C2:C6 * (B2:B10 = “Bricks”),2) and Ctrl+Enter