Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA HELP
I'm stumped by a simple problem. I want Excel to calculate a simple Z score
based on two criteria, Gender in cell C5 and AGE in cell F5. I want the formula to do this: If gender is Male, and age is X1, then zscore is Z1.,but if gender is Male, and age is X2, then zscore is Z2, but if gender is Male, and age is X3, then zscore is Z3... The confusing part for me is that I need the formula that does a simple calculation, but must chose one of six conditions depending upon gender and three age ranges. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA HELP
Jim,
A simple IF test =IF(C5="Male",IF(F5=X1,Z1,IF(F5=X2,Z2,IF(F5=X3,Z3, "")))) or you could construct a lookup table for the age and zscores, and use VLOOKUP =IF(C5="Male",VLOOKUP(F5,G1:H3,2,False),"") -- HTH RP (remove nothere from the email address if mailing direct) "Jim Michner" <Jim wrote in message ... I'm stumped by a simple problem. I want Excel to calculate a simple Z score based on two criteria, Gender in cell C5 and AGE in cell F5. I want the formula to do this: If gender is Male, and age is X1, then zscore is Z1.,but if gender is Male, and age is X2, then zscore is Z2, but if gender is Male, and age is X3, then zscore is Z3... The confusing part for me is that I need the formula that does a simple calculation, but must chose one of six conditions depending upon gender and three age ranges. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA HELP
Hi
=IF(C5="Male","Z","Y") & IF(C5="Male",MATCH(F5,{0,19,71},1),MATCH(F5,{0,19, 66},1)) or when you want to use "M#"/"F#" instead, then =UPPER(LEFT(C5,1)) & IF(C5="Male",MATCH(F5,{0,19,71},1),MATCH(F5,{0,19, 66},1)) Arrays in MATCH() are meant as examples, adjust them according your needs (and you can have up to 24 age groups when needed) Arvi Laanemets "Jim Michner" <Jim wrote in message ... I'm stumped by a simple problem. I want Excel to calculate a simple Z score based on two criteria, Gender in cell C5 and AGE in cell F5. I want the formula to do this: If gender is Male, and age is X1, then zscore is Z1.,but if gender is Male, and age is X2, then zscore is Z2, but if gender is Male, and age is X3, then zscore is Z3... The confusing part for me is that I need the formula that does a simple calculation, but must chose one of six conditions depending upon gender and three age ranges. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |