LARGE function - Skip value based on column data?

STEVEMILLS04

Board Regular
Joined
Oct 8, 2009
Messages
113
I am using the LARGE function and want to skip values if say Column B = "Not Human".

Are there any clever ways to handle this?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Yes.

One way is this:


Book1
ABCD
1valuecategoryman
232insect96
35protazoa
469fish
596man
622protazoa
748protazoa
847insect
933fish
1072protazoa
1145fish
125protazoa
1322insect
1470man
1516man
1677insect
1761man
1830insect
1968man
2043insect
2160man
2248fish
2384protazoa
Sheet29
Cell Formulas
RangeFormula
D2=LARGE(IF(B2:B23<>D1,A2:A23,""),1)
 
Upvote 0
DRSteele, I appreciate your response but your post contradicts itself. Your formula says "Give me the largest number if Column B is not equal to 'man'..." yet the number returned, 96, is that of row 5, which has 'man' in column B.

Am I confused?
 
Upvote 0
You aren't confused. I neglected to inform you to invoke the array formula properly with Ctrl+Shift+Enter. After doing so, the result will be 84 (Row23).

On second reading, I think you want the largest value that is EQUAL to D1, right? If that is so, alter the formula to = instead of <>


Book1
D
1man
284
Sheet29
Cell Formulas
RangeFormula
D2{=LARGE(IF(B2:B23<>D1,A2:A23,""),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top