Something about forest and trees?

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You have a ) in the wrong place
Code:
LEN(B402)<9
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
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?
 
Last edited:

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,424
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 17, 2002
Messages
345
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

Active Member
Joined
Feb 17, 2002
Messages
345

ADVERTISEMENT

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

Thanks!
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,424
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,585
Messages
5,523,734
Members
409,533
Latest member
rinogjb

This Week's Hot Topics

Top