Something about forest and trees?

[pilot]

Why can't I figure this out?

=IF(F402,IF(LEN(B402<9),IF(C402<501,C402*0.01,5+(C402-500)*0.006),33),"")

When LEN(B402<9 is FALSE, I want 33 as the result but I'm getting "".

Thanks for your assistance.

 

[pilot]

Sure, Joe.

This formula goes in K1.

If anything is in A1, complete the following tests, else display blank
If LEN B1<9 = TRUE,
If C1<501 = TRUE, C1*0.01
If C1<501 = FALSE, 5+(C1-500)*0.006
If LEN B1<9 = FALSE, C1

I think this shows all conditions. Thanks again for your assistance.

 

[Joe4]

OK, let's build it piece-by-piece, so you can see how it all works:

If anything is in A1, complete the following tests, else display blank

So, we start off with (just using "x" as a place-holder for the true statement):

=IF(A1,[COLOR=#ff0000][B]x[/B][/COLOR],"")

If LEN B1<9 = TRUE,
...
If LEN B1<9 = FALSE, C1

So we replace the "x" in our first formula with:

IF(LEN(B1)<0,[COLOR=#ff0000][B]y[/B][/COLOR],C1)

where y is our new placeholder.
So making that substitution now looks like:

=IF(A1,IF(LEN(B1)<0,[COLOR=#ff0000][B]y[/B][/COLOR],C1),"")

Lastly, we have this part:

If C1<501 = TRUE, C1*0.01
If C1<501 = FALSE, 5+(C1-500)*0.006

which would just look like:

IF(C1<501,C1*0.01,5+(C1-500)*0.006)

So substituting that in to "y" in our formula above yields:

=IF(A1,IF(LEN(B1)<0,IF(C1<501,C1*0.01,5+(C1-500)*0.006),C1),"")

So that is how you build a nested IF formula!

 

[Scott T]

Try

=IF(A1<>"",IF(LEN(B1)<9,IF(C1<501,C1*0.001,5+(C1-500)*0.006),C1),"")

 

[pilot]

Nice, thanks for the help!

 

[Joe4]

You are welcome.
I hope breaking it down like I did allowed you to follow along and make sense.