combining index with max - tried most posted solutions to no avail
Hi,
got 2 sheets depicting absence, one for each busines area.
Got a column for each day and a row for each employee divided in their
teams.
We have a column for each time they are absent using
=SUMPRODUCT((I2:HN2="S")*(I2:HN2<J2:HO2)) and a column counting each
day they are absent using =COUNTIF(I2:HN2;"S") .. we mark absence with
a S in the cell
now.... if I want to list the 10 individuals with highest absence
either in days or times of absence I can start with MAX but I cant get
the Index formula working together with MAX as displayed in earlier
similar topics.
I can use a third sheet to get a summary of the highest amount of
sickdays using
=MAX('BAXX'!G2:G26;'BAXX'!G32:G56;'BAXX'!G62:G86;' BAXX'!G92:G116;'BAXX'!G122:G146)
but how can I get the corresponding name of that value and how can I
get the top 10 values/names?
many thanks in advance
|