LARGE function - Skip value based on column data?

[STEVEMILLS04]

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?

 

[DRSteele]

Yes.

One way is this:

Book1
	A	B	C	D
1	value	category		man
2	32	insect		96
3	5	protazoa
4	69	fish
5	96	man
6	22	protazoa
7	48	protazoa
8	47	insect
9	33	fish
10	72	protazoa
11	45	fish
12	5	protazoa
13	22	insect
14	70	man
15	16	man
16	77	insect
17	61	man
18	30	insect
19	68	man
20	43	insect
21	60	man
22	48	fish
23	84	protazoa
Sheet29

Cell Formulas
Range	Formula
D2	=LARGE(IF(B2:B23<>D1,A2:A23,""),1)

 

[STEVEMILLS04]

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?

 

[DRSteele]

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
1	man
2	84
Sheet29

Cell Formulas
Range	Formula
D2	{=LARGE(IF(B2:B23<>D1,A2:A23,""),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[STEVEMILLS04]

Thanks, that's helpful.