Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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

=IF(COUNT(A3:C3)=3, A3-B3-C3, "")
share|improve this answer
    
I thought that there must be better way to do this and here it is. –  David Dec 5 '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 5 '12 at 21:00
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.