Search code examples
excelmaxlookupmode

Excel: Formula that returns 1st through 4th most common price

I've spent quite a bit of time trying to find a way to return the prices that have the most purchases for each city. Please see the below mock up workbook:

City,           Purchases,     Price  
New York City,      10,         $6  
Boston,             8,          $8  
Chicago,            12,         $6  
Boston,             7,          $9  
New York City,      9,          $5  
New York City,     16,         $3  
Houston,            4,          $12

So for example, I need three excel formulas to return for me the greatest number of purchases in NYC was at the $3 price, the second most was at $6 and the third most was at $5. I would need the same thing for Boston, Chicago and Houston with Excel recognizing that there is no 3rd greatest for Boston and nothing beyond first greatest for Chicago and Houston. There will never be any instances where the price is the same for the same city. I was able to get the greatest number of purchases using this formula: =MAX(IF((A:A="New York City"),C:C)), however, am unsure how to use a similar formula to this to get a possible second and third greatest price.

Any help or thoughts would be greatly appreciated!

Update

Thank you both for your answers, they were greatly appreciated and certainly would help solve this issue. I also received the formula below from another forum. Sorting is not required for this formula and I am unfamiliar with IFNA, which is why I chose to use this formula, but since it is an array formula you must click ctrl + shift + enter for it to work properly.

UPDATE

I ran into an issue with the last updates formula not working properly if the same city has the same number of purchases at different prices. The formula posted below solves this issue.

{=Index(C:C, MATCH(LARGE(IF(A:A=“New York City”,B:B+C:C*0,000000001), 1),IF(A:A=“New York City”,B:B+C:C*0,000000001),0))}

Solution

  • Edit: This answer returns the largest number of purchases, but not the prices for those most common purchases, as the requester was looking for.

    LARGE is designed for part of what you're looking for, so the next task might just be matching and creating the array for large. The following requires that the City column be sorted in ascending order, based on how the MATCH formula is set up, but should help get you well on your way.

    I solved it by starting with LARGE, and using two INDEX-MATCH formulas to create an array for large, and copy-pasting the formula into a matrix of cities vs. "largest number" indices.

    =LARGE(INDEX($B$1:$B$8,MATCH($A13,$A$1:$A$8,0)):INDEX($B$1:$B$8,MATCH($A13,$A$1:$A$8,1)),B$12)

    I put this in a table where I had the unique cities in the A range (starting at $A13), and the index number for the LARGE formula in the "12" row starting at B$12. I ended up with a matrix of cities and 1st, 2nd, and 3rd largest sale prices.

    A     B     C     D
      1     2     3
B    f(x)  f(x)  f(x)
C    f(x)  f(x)  f(x)
H    f(x)  f(x)  f(x)
NYC  f(x)  f(x)  f(x)

    The formula will return #NUM! if you don't have enough data (e.g., the LARGE formula won't return something valid if you ask for the 3rd largest, but you only have 2 pieces of data).

    enter image description here