Re: FORMULA HELP

From: Arvi Laanemets (garbage_at_hot.ee)
Date: 12/29/04 

Date: Wed, 29 Dec 2004 14:01:41 +0200

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 Michner@discussions.microsoft.com> wrote in message
news:1189A24C-64B8-44E9-B9C5-87E037CDAA67@microsoft.com...
> 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.

Relevant Pages