Maximum value index function

[Jono_NZ]

Hi,

I'm trying to return whether a given value is the maximum value in it's respective category. Is there a way to include the max function in a sumproduct, index formula?

Thanks

Column A Column B Column C
Category Value Max. Value?
Apples 370 No
Apples 501 No
Apples 914 Yes
Apples 572 No
Apples 327 No
Oranges 17
Oranges 870
Oranges 296
Oranges 661
Oranges 116
Oranges 974
Pears 404
Pears 328
Pears 343
Pears 85
Pears 464
Pears 201
Pears 589
Pears 392

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

This will give you a True or False instead of yes or no
=B1=MAX(IF($A$1:$A$20=A1,$B$1:$B$20))

Hope that helps.

 

[mikerickson]

=IF(B1=MAX((A1:A100=A1)*B1:B100),"Max","not")

 

[Jono_NZ]

Thanks this works great.