3

I have a scenario where I don't want excel to use a blank cell as a zero value. I only want the cell to display the result if all the cells in the formula have a value entered.

For example: =A3-B3-C3

When only A3 and B3 have values entered, I would like the cell to remain blank and show no answer until C3 is filled in.

Any suggestions appreciated.

Thanks LT

lt78
  • 31

3 Answers3

5

As a trivial extrapolation of this very recent question/answer,

=IF(COUNT(A3:C3)=3, A3-B3-C3, "")
Community
  • 1
Scott - Слава Україні
  • 21,717
  • I thought that there must be better way to do this and here it is. – David Dec 05 '12 at 19:22
  • +1 for simplest answer. Although the question assumes all the cells will be numbers (given the math A3-B3-C3) if you wanted to use this for text manipulation or more complicated formulas then it may fail. Jikag's answer would work for just about anything in those cells. – techturtle Dec 05 '12 at 21:00
2

You can combine an OR statement with an ISBLANK statement and then wrap it all in an IF statement.

To begin, we need to check if a cell is blank, you can do this like so:

=ISBLANK(A3)

This will return FALSE if the cell is full and TRUE if the cell empty.

Next you want to check if all three cells are blank. This can be done with an OR statement, like so: 

=OR(ISBLANK(A3),ISBLANK(B3),ISBLANK(C3))

OR checks if all arguments are TRUE, and returns TRUE or FALSE. It returns FALSE if all arguments are FALSE, which is what you want.

Finally, you need to evaluate the statement and return your answer only if all cells have been filled in, otherwise the cell will remain blank. To do this, you use an IF statement, like so:

=IF(OR(ISBLANK(A3),ISBLANK(B3),ISBLANK(C3)), "", A3-B3-C3)
David
  • 9,334
-1

David you are amazing. I used your answer with another formula and it worked beautifully! For calculating dates, and using date functions, you may know that the functions calculate even if no value is in the original date cell. So here is how to show nothing in a cell when automatically calculating dates until a start date is input (of course, replace with any function you may need):

=IF(ISBLANK(R4),"",EDATE(R4,6))
Burgi
  • 6,551
Vanessa
  • 1