Nested If/And/Or statements

[Belinda]

=IF(AND(B6="FT",(IF(D6>4,15,IF(D6>=2,10,IF(D6>=2,10,IF(D6>=1,5,0)

Hi. I am trying to create a formula to return the values above if the
"B6="FT" is true. If true return the values for the nested if statement, else
return zero. I either get an error or "True" or "False". I want to return
either 15, 10,5, or zero. Can you help me fix this formula to give me the
results I need?

Thank you!

 

[JP Ronse]

Belinda,

Try this

if(B6="FT", if(D6>4,15,if(D6>=2,10,if(D6>=1,5,0,""))),"")

Wkr,

JP

 

[Don Guillett]

=IF(D6>=4,15,IF(D6>=2,10,IF(D6>=1,5,0)))
=IF(B6="ft",IF(D6>=4,15,IF(D6>=2,10,IF(D6>=1,5,0))),"")

 

[Bob Phillips]

Try

=IF(B6="FT",(IF(D6>4,15,IF(D6>=2,10,IF(D6>=1,5,0)))))

HTH

Bob

 

[Joe User]

=IF(AND(B6="FT",(IF(D6>4,15,IF(D6>=2,10,IF(D6>=2,10,IF(D6>=1,5,0)
[....] I am trying to create a formula to return the
values above if the "B6="FT" is true. If true return
the values for the nested if statement, else return zero.

You state quite clearly that you want zero if B6<>"FT". You also state
quite clearly that you want only the results "15, 10,5, or zero". So:

=IF(B6<>"FT", 0,
IF(D6>4,15,IF(D6>=2,10,IF(D6>=1,5,0))))

There are alternative formulas that avoid that deep function nesting,
depending on what you know about the values in D6. For example:

=IF(B6<>"FT", 0,
LOOKUP(D6,{-1E307,1,2,4},{0,5,10,15}))

-1E307 should cover all values of D6<1. But if you know that D6 is, say, no
less than 0, replace -1E307 with 0 for a more readable formula.


----- original message -----