CODEWITHSUNDEEP
excelexcel-formula
Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Finding largest 2 values in a range using excel formula

I have data like below

Classifiaction              Hours
Home                157.40
Home                157.39
Mens Apparel        157.39
Mens Apparel        157.39
Sunglasses          157.39
Sports              157.33
Biss                157.33
Mens Apparel IA     157.32
Mens Apparel IA     157.32
Kitchen             157.32
Beauty              157.32
Home                157.32
Home                157.31
Mens Apparel        157.31
Mens Apparel        157.31
Sunglasses          157.31
Sports              157.29
Biss                157.29
Mens Apparel IA     57.29
Mens Apparel IA     157.29
Kitchen             157.28
Beauty              157.28

From this i looking for a solution to find largest two hours under each category.

Like below

Beauty  157.32  '1st largest value in under beauty
Beauty  157.28  '2nd largest value in under beauty

Mens Apparel    157.39  '1st largest value in under Mens Apparel
Mens Apparel    157.39  '2nd largest value in under Mens Apparel

Using large function.i'm able to find largest two hours in whole range but not for each category(Classification).

Upvotes: 1

Views: 68

Answers (1)

xificurC
xificurC

Reputation: 1178

This will work if your numbers are always non-negative:

=LARGE(($A$2:$A$23="Beauty")*$B$2:$B$23,1)

I'm assuming your data starts in A1 and the formula should be entered as an array formula (press CTRL+SHIFT+ENTER instead of just ENTER and your formula should appear in curly braces {=LARGE...}.

What I would do is copy column A somewhere, remove duplicates and put a 1 in the next column for them, then copy the whole thing underneath itself and put 2 instead of 1 and sort if afterwards. This way you can build your formulas based on this table

Beauty          1
Beauty          2
Biss            1
Biss            2
Home            1
Home            2
Kitchen         1
Kitchen         2
Mens Apparel    1
Mens Apparel    2
Mens Apparel IA 1
Mens Apparel IA 2
Sports          1
Sports          2
Sunglasses      1
Sunglasses      2

Upvotes: 2

Related Questions