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.

 

[Scott T]

You have a ) in the wrong place

LEN(B402)<9

 

[KennyGreens]

Maybe I am missing something, but what is the point of the first part of the formula, what is the logical test of F402, just to see if there is something there?

 

[pilot]

KennyGreens, exactly.

Scott T, I figured that would be my problem. But I made this change and still get "".

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

 

[Joe4]

The way you have written it, the "33" is associated with the FALSE part of when C402 < 501
and the "" is associated with the FALSE part of when LEN(B402) < 9
So change your 33 and "" accordingly.

 

[pilot]

I want the "" to be associated with the FALSE part of IF(F402...) so it needs to be at the very end. Or maybe I'm not understanding your suggestion.

 

[pilot]

Joe4, I did what you suggested and it magically fixed the issue, but I don't understand why.

Thanks!

 

[Joe4]

The IF statement has both a TRUE and a FALSE argument. When you nest them, the TRUE/FALSE argument for each nested IF may not be right next to each other.
Work from the inside out (starting with the inner-most IF).

Here is a detailed explanation of nested IF functions: https://support.office.com/en-us/ar...pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8

 

[pilot]

=IF(F403,IF(LEN(B403)<9,IF(C403<501,C403*0.01,5+(C403-500)*0.006),""),33)

Sorry folks, it still isn't right. Now, if LEN is TRUE, I'm getting 33 instead of C403*0.01. This is baffling me.

 

[Joe4]

Can you please just list out all the conditions/criteria in plain English for us, describing what should happen in each instance, and what the order should be?
I find it is much easier to write the formula based on the conditions, then try to guess what the user wants to happen from errant formulas.