Combining an IF and MAX function

  • Thread starter Thread starter Drummy
  • Start date Start date
D

Drummy

ok, say I have this worksheet

Gemma Senior 1.1
Mark Senior 1.0
Giles Senior 1.1
Matthew Senior 0.2
Owen Senior 0.7
Kevin Senior 1.8
Alex Senior 0.0
Valentina Senior 1.3
Katie Junior 1.3
Ashlea Junior 0.2
Tara Senior 0.0
Tahlia Sub Junior 0.9
Tyler Sub Junior 1.0
Kylie Junior 1.6
Mark Senior 0.0

first column (C2:C16) is the name of people in my tennis club

second row (D2:D16) is their division

and third row (J2:J16) is their ongoing score

what would the formula be if I wanted to put the leading top scorer for
each division (senior, sub junior or senior) into three seperate cells?
 
Hi Drummy,

In cells B18-B20, enter "Senior", "Junior" and "Sub Junior", respectively.

In cell A18 enter the array formula:
=INDEX(A$2:A$16,MATCH(C18,IF(B$2:B$16=$B18,C$2:C$16,),0))
and copy down to row 20.

In cell C18 enter the array formula:
=MAX(IF(B$2:B$16=$B18,C$2:C$16,))
and copy down to row 20.

Note: array formulae are created with <Ctrl-Shift-Enter> instead of <Enter>

Cheers
 
wooooooooooooohoooooooooooooooooo!!

Fantastic!!, IT WORKED!!

Mr Macropod, you are truly an intelligent and legendary entity,

With you're fingertips you have alleviated much anxiety and annoyance,

THANK YOU!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top