Finnishing times and grades in excel

[rmork]

Hi...

I would like excel to figure out the grade for students on a given finnishing time on a track race...

Example:

the finnishing time < 00:05:20 would give the grade 6
the finnishing time < 00:06:20 would give the grade 5
the finnishing time < 00:07:20 would give the grade 4

I now have the if statement looking like this, but I do not understand how it is workin, but it is:

=HVIS(L18<$AD$2;6;HVIS(L18<$AE$2;5; HVIS(L18<$AF$2;4;HVIS(L18<$AH$2;3;""))))

The HVIS is the same as IF (Norwegian)

I would like to change the time for the grades as the students gets older. How?
Anyone?

 

[mintjulep]

A table lookup would be one way.

Ages across the top row of the table. Times in the first column. At each intersection is the grade.

Search this site for two-dimensional lookup and INDEX and MATCH for examples.

 

[yogia]

Hi rmork:

Following is an example of the use of a LookUp Table as mentioned by mintjulep

[tt]         F              G                   H         I[/tt]
-----------------------------------------------------------
1|[tt]	RunnerTime GradeAssigned           LookUp     Table[/tt]
2|[tt]	0:05:11	   Gtrade6		      0:00:00	Gtrade6	[/tt]
3|[tt]	0:06:48	   Gtrade4		      0:05:20	Gtrade5	[/tt]
4|[tt]	0:07:42	   notAssigned		  0:06:20	Gtrade4	[/tt]
5|[tt]	0:07:05	   Gtrade4		      0:07:20	notAssigned	[/tt]
6|[tt]	0:04:59	   Gtrade6				[/tt]

Formula in cell G2 is ... =VLOOKUP(F2,$H$2:$I$5,2)
this is then copied down.

I hope this helps

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com