IF statement help

[Ruinline]

I have a workbook that i am using the following IF statement in
=IF(('Account Accuracy Rate'!D4)>=95,3,IF(AND(('Account Accuracy Rate'!D4)<95,('Account Accuracy Rate'!D4)>=85),2,IF(AND(('Account Accuracy Rate'!D4)<85),1,0)))

It always returns the value of 1 reguardless if the reference field is above 95, between the range or 95-85 or below 85.

What am i doing wrong?

The field i am using is using conditional formating (stop light) hence the reason for the 1,2,3 values if condition is true.

Thanks!

D

 

[JamesW]

Hi,

Welcome to the board.

Try:

=IF('Account Accuracy Rate'!D4>=95,3,IF(AND('Account Accuracy Rate'!D4>=85,'Account Accuracy Rate'!D4<95),2,IF('Account Accuracy Rate'!D4<85,1)))

 

[barry houdini]

What's in D4? If you have percentages in there then the formula should refer to 0.95 rather than 95 etc. You can shorten like this, though

=IF('Account Accuracy Rate'!D4>=0.95,3,IF('Account Accuracy Rate'!D4>=0.85,2,1))

 

[Ruinline]

You so nailed it! Totally spaced the .95 for percentage. Thanks for the wake up call.

 

[Jonmo1]

You don't need to use AND when testing AND( > 85, < 95) Because If the value is NOT < 95, then the >=95 would have been true, and the first IF would have returned 3 and ignored the next if.
So you only need to test for >85

And again, you don't need to test for <85, because the previous 2 ifs(>=95 and >=85) would have already captured it.

Try
=IF('Account Accuracy Rate'!D4>=0.95,3,IF('Account Accuracy Rate'!D4>=0.85,2,1))


And Finally, try LOOKUP instead

=LOOKUP('Account Accuracy Rate'!D4,{0,0.85,0.95},{1,2,3})

Presuming there are no negative values..

If there are negatives, replace 0 with -1E-307


Hope that helps.