Nested IF Formula returning incorrect result

New Contributor

=IF($C$4=10,"Prohibited",IF(AND($C$4>7,$C$4<=9),"PEP",IF($C$4>3,"High",IF($C$4>2,"Medium",IF($C$4>0,"Low","")))))

3 Replies
Sorry I should say it is returning a result of "high" even if the cell it is looking contains 0.

@Claire Fox 

=IF(C4=10,"Prohibited",IF(AND(C4>7,C4<=9),"PEP",IF(C4>3,"High",IF(C4>2,"Medium",IF(C4>0,"Low","")))))

I removed the dollar signs to fill the formula down and it returns an empty cell for 0 and for values greater 9 and <> 10 the formula returns "High".

nested if.JPG

 

The only cell that formula will ever look at is C4. Using the $ symbol before a column/row in a formula like that makes it an absolute reference - i.e. look here, always here; no matter where you copy and paste this formula to, it will always look at the value in C4.

 

Remove all of the $ symbols from your formula, and it becomes a relative reference. it will look at whatever the value is in column C of the same row that the formula is in.  

 

Absolute references are good for things that are always the same, no matter what, say, sales tax.  Relative references are when you have a value that is specific to that individual record/row, like quantity.