CODEWITHSUNDEEP
excelexcel-formula
Tom Sutherland
Tom Sutherland

Reputation: 53

Excel IF statement is returning "FALSE"

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?

Upvotes: 1

Views: 7177

Answers (4)

Solar Mike
Solar Mike

Reputation: 8375

Ok, had a go at this just to see:

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

enter image description here

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:

enter image description here

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 7951

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: Flow Chart of If Statement Logic

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, ".", "")

Upvotes: 3

Xlsx
Xlsx

Reputation: 175

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.

Upvotes: 0

JLCH
JLCH

Reputation: 803

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

Upvotes: 3

Related Questions