Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |