How to use a different formula depending upon a LOOKUP result

[Barty]

Hi,

I am making a spreadsheet to use on client's to calculate dietary requirements. I am attempting to get one cell to perform two functions, whereby it must lookup the client's gender from another cell, then use this information to identify which formula to use to calculate if the client is at risk and display either "at risk" or "normal".

For females the range is 0.8 to 0.9 (however for the purpose of this you can just use >0.8)
For males the range is 0.9 to 1.0 (or >0.9)

First it needs to identify the gender from cell D7 so I used: (* denotes what excel should do if the result is found true)

=LOOKUP(D7,{"Female","Male"},{*,*})

But when I tried to add in the formula instruction it did not work. What I need it to do from here is identify the client is female, so use the formula >0.8 to identify "At risk" if this is true, or "normal" if this is false.


=LOOKUP(D7,{"Female","Male"},{(IF(D14>=0.8,"At Risk","Normal"),0.9})

help please

 

[Michaels]

A simple IF can solve it, but I didn't get what you want to do ? You are looking in D7 and if its Male, then what will happen ?

 

[Barty]

A simple IF can solve it, but I didn't get what you want to do ? You are looking in D7 and if its Male, then what will happen ?

Thanks Shawn.I'll give you some more info...

I have one cell which calculates the client's waist-to-hip ratio and gives a value up to 1.0
beneath that is a cell for classification. i want this cell to lookup D7 and then....

If it is male, then I want it to classify a score above 0.9 as "at risk", anything else "normal"
If it is female, then I want it to classify a score above 0.8 as "at risk", anything else "normal"

does that make more sense?

 

[Aladin Akyurek]

In which cell is the calculated waist-to-hip ratio?

 

[Michaels]

Hi Barty !

Is this what you looking for:

[TABLE="width: 291"]
<tbody>[TR]
[TD]Patient Name[/TD]
[TD]Gender[/TD]
[TD]Waist-to-Hips Ration[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Patient 1[/TD]
[TD]Male[/TD]
[TD="align: right"]0.8[/TD]
[TD]Normal[/TD]
[/TR]
[TR]
[TD]Patient 2[/TD]
[TD]Male[/TD]
[TD="align: right"]0.91[/TD]
[TD]At Risk[/TD]
[/TR]
[TR]
[TD]Patient 3[/TD]
[TD]Female[/TD]
[TD="align: right"]0.75[/TD]
[TD]Normal[/TD]
[/TR]
[TR]
[TD]Patient 4[/TD]
[TD]Female[/TD]
[TD="align: right"]0.9[/TD]
[TD]At Risk[/TD]
[/TR]
[TR]
[TD]Patient 5[/TD]
[TD]Female[/TD]
[TD="align: right"]1[/TD]
[TD]At Risk[/TD]
[/TR]
</tbody>[/TABLE]

Considering the above data from Column A to D, in D2 (from above table's perspective), try:

=IF(AND(B2="Male",C2>0.9),"At Risk",IF(AND(B2="Female",C2>0.8),"At Risk","Normal"))

 

[Barty]

Hi Barty !

Is this what you looking for:

[TABLE="width: 291"]
<tbody>[TR]
[TD]Patient Name[/TD]
[TD]Gender[/TD]
[TD]Waist-to-Hips Ration[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Patient 1[/TD]
[TD]Male[/TD]
[TD="align: right"]0.8[/TD]
[TD]Normal[/TD]
[/TR]
[TR]
[TD]Patient 2[/TD]
[TD]Male[/TD]
[TD="align: right"]0.91[/TD]
[TD]At Risk[/TD]
[/TR]
[TR]
[TD]Patient 3[/TD]
[TD]Female[/TD]
[TD="align: right"]0.75[/TD]
[TD]Normal[/TD]
[/TR]
[TR]
[TD]Patient 4[/TD]
[TD]Female[/TD]
[TD="align: right"]0.9[/TD]
[TD]At Risk[/TD]
[/TR]
[TR]
[TD]Patient 5[/TD]
[TD]Female[/TD]
[TD="align: right"]1[/TD]
[TD]At Risk[/TD]
[/TR]
</tbody>[/TABLE]

Considering the above data from Column A to D, in D2 (from above table's perspective), try:

=IF(AND(B2="Male",C2>0.9),"At Risk",IF(AND(B2="Female",C2>0.8),"At Risk","Normal"))

Awesome! Thanks Shawn, that works perfectly!!