IF and Lookup

[Ken Quick]

I need to find a way to calculate the performance of a student doing a curl
up challenge based upon the number of curl ups they do and the age of the
student.

Because the pass/fail rate changes with age, I think I need to use another
worksheet to reference the standards. For example age 8 needs 18 curl ups
and age 10 needs 20. And Girls is another story.

I want to do a IF statement that will state a pass or fail, but the varying
age of students in a class cause a problem. Can I lookup a table using Age
to reference a age related standard to determine the IF statement that will
show if the student passes or fails.

Any help would be great. I would give more excel related information, but I
am not sure how to do this.
Example Marc D age 8 does 18 curl ups which equals the 8 year old standard
of 18 but David age 10 does 1 curl ups which does not meet the 10 year old
standard of 22.

 

[bpeltzer]

Don't look up the if statement to use, but rather to look up the appropriate
value to test within a single if statement...
Something like =if(c2>=vlookup(b2,AgeTable,2,true),"Pass","Fail")
(Assumption above is that column b contains the age, column c contains the
number of curl-ups performed, and the AgeTable is a two-column table with the
age and then the standard, in ascending order of age).

 

[ShaneDevenshire]

Hi,

Suppose you set up a table like this starting in cell A1

A B C
Age Male Female
8 18 12
10 20 14
12 22 16
14 24 18
16 26 20

In this example I will keep the table on the same sheet as the student scores.

Suppose the students are listed like this starting in cell E1:

E F G H
Gender Age Curls Pass/Fail
Female 10 18 Pass
Male 12 17 Fail

The formula in cell H2 is

=IF(G2>=VLOOKUP(F2,$A$2:$C$6,MATCH(E2,$A$1:$C$1,0),TRUE),"Pass","Fail")

Copy the formula down.