FORMULA HELP

[Guest]

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.

 

[Bob Phillips]

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)

 

[Arvi Laanemets]

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