IF formula output not expected

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
134
I am using 2 embedded IF statements in the attempt to produce either a string (cell&cell...) or a string with -SPARE are the end.

I know the inner IF statement works as expected. The result is setup to work on the FALSE, not TRUE for desired output.

My outer IF works when I do not include the inner IF, so I am guessing I have something out of alignment, or I am just not performing the correct embedded IF sequence.

Code:
=IF('Table'!F23="TRUE",IF($B$17="########",L56&"-BI"&M56&"-"&C56&"-SPARE",L56&"-BI"&M56&"-"&C56),L56&"-BI"&M56&"-"&C56&"-SPARE")
The inner
Code:
IF($B$17="########",L56&"-BI"&M56&"-"&C56&"-SPARE",L56&"-BI"&M56&"-"&C56)
Will string&-SPARE the line when cell B17 is full of # marks, else will populate with correct values.

The otter loop:
Code:
IF('Table'!F23="TRUE",L56&"-BI"&M56&"-"&C56,L56&"-BI"&M56&"-"&C56&"-SPARE")
Also will check correctly and either populate the string, or populate string&-SPARE as expected.

When I combine them the only output is string&-SPARE, that is not my desired outcome.

Thank you in advance for the help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
How about
=L56&"-BI"&M56&"-"&C56&IF(Table!F23=TRUE,IF($B$17="########","-SPARE",""),"-SPARE")
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
The inner
Code:
IF($B$17="########",L56&"-BI"&M56&"-"&C56&"-SPARE",L56&"-BI"&M56&"-"&C56)
Will string&-SPARE the line when cell B17 is full of # marks, else will populate with correct values.
Really ? I find that hard to believe.
That formula tests whether B17 contains the text string "########", so should evaluate to FALSE if B17 contains the text strings "#######" or "#########" or "###" for example.
If B17 is really just full of #marks , that is usually an indication that B17 contains a number of some kind (perhaps a time or date) that Excel can not display correctly because the column width is too small.
And that can normally be addressed by making the column wider.
If that's what you mean, then
IF($B$17="########"
doesn't evaluate to TRUE in that situation.
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
134
Fluffy, that was perfect. Not sure why changing the order like that works, but it is exactly what I was after.

Really ? I find that hard to believe.
That formula tests whether B17 contains the text string "########", so should evaluate to FALSE if B17 contains the text strings "#######" or "#########" or "###" for example.
If B17 is really just full of #marks , that is usually an indication that B17 contains a number of some kind (perhaps a time or date) that Excel can not display correctly because the column width is too small.
And that can normally be addressed by making the column wider.
If that's what you mean, then
IF($B$17="########"
doesn't evaluate to TRUE in that situation.
Higgins, yes I truly do me #### that is a bit of hard code we use on a reference worksheet to indicate null value for that cell. if that cell is null on the reference sheet we want the string&-SPARE on all sheets pointing to that value.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
The problem with your formula was that you had True in quotes which means it was looking for a text value, rather than a logical value.
I just took the opportunity to simplify the formula at the same time.
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
134
I want to make sure I am following my error.

by having "TRUE" or "FALSE" that can lead to valuation errors as those are treated as text not Boolean logic values
Question, with text values could that lead to formulas not updating after a copy/paste value only?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
by having "TRUE" or "FALSE" that can lead to valuation errors as those are treated as text not Boolean logic values
That's right.
Copy/paste a values is fine, because the value will still be a boolean value, rather than text.
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
134
Thank you, I will adjust my formulas and hopefully that will reduce the lack of updating I am seeing when I copy/paste values only. My formulas do not update currently with "TRUE"/"FALSE" unless I type the values in a 2nd time. very much defeats the reason for copy/paste values IMHO.

edit to add: Made the change from text to Boolean, poof problem solved.

Thank you again Fluffy
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top