Function considering time record and returning a grade

Hi everyone!
I have problem with building up a function which return a grade for fitness test result but considering age band for each individual. There is 7 age bands and 4 different grades. Results of the fitness test (run) are written in following format - '15:35'. I need Excel to return a grade for a run for certain individual but considering age band. Someone suggested to do index and match but I am not proficient at it. I managed to build nested IF AND function with 28 conditions but because this result is in time format, the function doesn't recognize a numbers properly. Any suggestions? I really appreciate any help on this

Hello AnnaS87. Welcome to the forum.

Please try this formula in D7 and fill down.

Formula:

Please Login or Register  to view this content.

	B	C	D
6	Age Band	3.2 Run	Grade
7	C	15:06	1.00
8	A	15:05	3.00
9	G	15:04	1.00

but because this result is in time format, the function doesn't recognize a numbers properly. Any suggestions? I really appreciate any help on this

Originally Posted by AnnaS87

Hi FlameRetired, Thank you for getting back to me. Function seems to work however I am after doing something with formatting my cells and this function does not want to work in my original workbook. I am not sure what I am doing wrong with it.

It sounds like you have not tried my formula. It will take care of that. It works at my end.

If you are not aware of it dates and times are numbers. In the case of times they are decimal fractions of a day. The formatting is cosmetic only ... for human eyes. When times/numbers are entered as text (as yours are) they have a numeric value of 0. To coerce those text "numbers" into their underlying numeric values simply subject them to a math operation ... or ... precede them with "--" (a double unary). See the formula. It has those.

=IFERROR(INDEX($F$9:$F$12,MATCH(--C7,INDEX(--SUBSTITUTE($G$9:$M$12,"<",""),,MATCH(B7,$G$7:$M$7,0)),1)),1)

Of course it is always best to take pains to enter numbers including dates and times as numbers.