Excel IF statement is returning "FALSE"

Question

I am trying to write a statement which will read the content of the adjacent cells and select a value depending on the results to punctuate a paragraph. Just like the below sentence is laid out.

Statement 1, Statement 2, Statement 3.

A comma is included if the next statement has content. A full stop is included if the next statement does not have content. Nothing is included if the next and previous statements do not have content. I have used the formula below, but the bit that's supposed to add the full stop is returning FALSE:

=IF(A1<>"",IF(C1<>"",", "),IF(A1<>"",IF(C1="",". "),IF(A1="",IF(C1="",""))))

What have I done wrong?

you're missing several [value_if_false]. If your if-tests go this way and no [value_if_false] is defined, the formula will return "false" as a result — JLCH
– JLCH, Commented Jun 4, 2019 at 7:41
Thanks @Jo.lass but I'm not sure I understand what you mean. It shouldn't be returning a false value anyway, it should find this part to be true? IF(A1<>"",IF(C1="",". ") — Tom Sutherland
– Tom Sutherland, Commented Jun 4, 2019 at 7:48

JLCH · Accepted Answer · 2019-06-04 07:50:24Z

3

I believe you have overcomplicated your formula. For example you test up to 3 times if A1 is blank

Are you trying to achieve this ?

=IF(A1<>"",IF(C1<>"","; ",""),IF(C1="",". ",""))

if A1 is not blank and C1 is not blank = ;
if A1 is not blank and C1 is blank = nothing
if A1 is blank and C1 is blank = .
if A1 is blank and C1 is not blank = nothing

answered Jun 4, 2019 at 7:50

JLCH

8031 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tom Sutherland Over a year ago

Thanks @Jo.lass, that's almost right. I'm trying to achieve: if A1 is not blank and C1 is not blank = , if A1 is not blank and C1 is blank = . if A1 is blank and C1 is blank = nothing

Tom Sutherland Over a year ago

Based on your formula I've just tried this which works :-) =IF(A1<>"",IF(C1<>"",", ",". "),IF(C1="","","")) Thanks again for your help!

JLCH Over a year ago

Yes, wasn't sure which one is right ;) thanks for the feedback

Chronocidal Over a year ago

Curses, foiled again - I took too long drawing out a flowchart of the logic tree

Chronocidal · Accepted Answer · 2019-06-04 09:39:23Z

3

The IF statement has this format: =IF(<Statement>, <Value_If_True>, <Value_If_False>)

If we break your code down in the same way, we get this:

    =IF(<Statement1>, IF_TRUE1(<Statement2>, <Value_If_True2>), IF_FALSE1(<Statement3>, IF_TRUE3(<Statement4>, <Value_If_True4>), IF_FALSE3(<Statement5>, IF_TRUE5(<Statement6>, <Value_If_True6>))))

The missing <Value_If_False> will return FALSE by default.

Now, that might be a bit hard to read, so here's another layout:

Hopefully you can see all of the duplicate "question" nodes there - and also that the "." is impossible to reach, because it requires that A1<>"" is FALSE, and also TRUE.

Rewriting your code, there is still 1 "missing" terminator:

=IF(A1="", IF(C1="", "", FALSE), IF(C1="", ".", ", "))

(Or, if you want to be really fancy, use a CHOOSE statement:)

=CHOOSE(1+(A1="")+2*(C1=""), ", ", FALSE, ".", "")

edited Jun 4, 2019 at 9:39

answered Jun 4, 2019 at 8:02

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

1 Comment

JLCH Over a year ago

Damn, that's a great explanation of the original issue - very visual +1

5202456 · Accepted Answer · 2019-06-04 07:54:42Z

0

This part of your formula seems redundant as it is already captured in the initial logical arguments:

IF(A1<>"",IF(C1="",". "),IF(A1="",IF(C1="","")))

I might not understand your objective entirely, but this should do the trick: I'm assuming statements here are defined by inputs into columns A,B & C and column A has to be filled first.

=IF(A1="","",IF(AND(B1="",C1=""),". ",", "))

If I'm way off, do share your table/sheet structure with desired results.

edited Jun 4, 2019 at 7:54

5202456

97214 silver badges25 bronze badges

answered Jun 4, 2019 at 7:51

Xlsx

1751 silver badge14 bronze badges

Comments

Solar Mike · Accepted Answer · 2019-06-04 10:48:53Z

0

Ok, had a go at this just to see:

=IF(AND(A1="",C1=""),"",IF(AND(A1<>"",C1<>""),A1&";"&C1,A1&C1&"."))

And if both are not blank, then they should be separated by ";" and finished with "." ...

=IF(AND(A1="",C1=""),"",IF(AND(A1<>"",C1<>""),A1&";"&C1&".",A1&C1&"."))

See:

edited Jun 4, 2019 at 10:48

answered Jun 4, 2019 at 10:37

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges

Collectives™ on Stack Overflow

Excel IF statement is returning "FALSE"

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related