nested if function

[kim]

I am trying to create a formula in excel to call a result as either wild type
(WT), heterozygous (HET) or homozygous (HOM). This is how I was going about
it, but Excel tells me that I have too many arguments for this function. (The
value in cell W11 is based on a function).
=IF(W11<=0.05,"WT",IF(AND(0.48<=W11,W11<=0.6),"HET"),IF(W11>=1,"HOM"))

Basically, I want it to give me the calls using the following ranges. If
you or someone else can suggest another approach (without use of a macro),
that would be greatly appreciated.

WT 0.00-0.05
HET 0.48-0.60
HOM 1.00-1.07

Optimally, it would be nice to have some comment, such as 'review' or
'out-of-range' for any value that didn't fall within the above ranges.

Thank you.

 

[Sam Wilson]

=IF(W11<=0.05,"WT",IF(AND(0.48<=W11,W11<=0.6),"HET",IF(W11>=1,"HOM","")))

Or use Vlookup?

 

[Jacob Skaria]

Try the below

=IF(AND(W11>=0,W11<=0.05),"WT",IF(AND(W11>=0.48,W11<=0.6),"HET",IF(AND(W11>=1,W11<=1.07),"HOM","Out of Range")))

'to handle blank entries in W1
=IF(W11="","",IF(AND(W11>=0,W11<=0.05),"WT",IF(AND(W11>=0.48,W11<=0.6),"HET",IF(AND(W11>=1,W11<=1.07),"HOM","Out of Range"))))

If this post helps click Yes

 

[Eduardo]

Hi,
you have a ) in the wrong place

=IF(W11<=0.05,"WT",IF(AND(0.48<=W11,W11<=0.6),"HET",IF(W11>=1,"HOM")))

 

[Per Jessen]

Hi

This is a paranthesis problem!
Remove the closing paranthesis after "HET" and place it at the very end of
the line.

To get an 'Out of range' message use this (one line):

=IF(AND(W11>=0,W11<=0.05),"WT",IF(AND(0.48<=W11,W11<=0.6),"HET",IF(AND(W11>=1,W11<=1.7),"HOM","Out
of range")))


Regards,
Per

 

[kim]

Thank you all for your responses. They all helped! It is great to have this
resource.