IF and Lookup
 

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.

IF and Lookup
 

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).

"Ken Quick" wrote:

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.

IF and Lookup
 

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.

--
Thanks,
Shane Devenshire


"Ken Quick" wrote:

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.