VLookup/IF Statements and Multiple Variable Lookup Not Working Correctly

[gustafsoni]

Hi,

I'm a technology teacher trying to help our physical education teachers be just a little more efficient with their fitness testing. The fitness grading requires looking at 2 variables before calculating scores:
- Student's ***
- Student's age

After looking at those variables, the sheet will then compare the student's performance to a set group of standards based on the *** and age. Our end goal is to have the teachers input student performance and be given a output grade (x-, x, x+) depending on the student performance. Currently, the spreadsheet we have created does not return the correct grades (i.e. a 9 year old girl runs a 6:00 mile and only gets a x, not an x+). That is just one of the errors. A sample function looks like this:

=+IF($C3="M",IF(E3>VLOOKUP(DATA!$D3,MALE,2),"x-",IF(DATA!E3<=VLOOKUP(DATA!$D3,MALE,3),"x+","x")),IF(DATA!E3>VLOOKUP(DATA!$D3,FEMALE,2),"x-",IF(DATA!$D3<=VLOOKUP(DATA!$D3,FEMALE,3),"x+","x")))

We're currently using Office 2003.

The Norms or Fitness Standards are on a separate sheet than the inputted data/class lists. I can email the file if you need more details. Thank you for any advice as grades are approaching!

[NBVC]

Is the 6 mile entered as 6.0 or 6:00? (there's a difference).... the 6:00 is interpreted by Excel as a time and is really 0.25 (if you change the format to general).

Also, each of your Vlookup() statements should probably have a fourth argument, labeled FALSE... this will find exact match and not closest matches...which may be causing your problem.... e.g. VLOOKUP(DATA!$D3,MALE,2,FALSE)