Excel: formula to display answer only if ALL cells in a formula have a value

Question

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

Solution 1

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

=IF(COUNT(A3:C3)=3, A3-B3-C3, "")

Solution 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)

Comments

[David]

I thought that there must be better way to do this and here it is.

[Scott - Слава Україні]

@fixer1234: No, A3&B3&C3="" is a slight shortcut for AND(ISBLANK(A3),ISBLANK(B3),ISBLANK(C3)).  The question is asking for a good way to test AND(NONBLANK(A3),NONBLANK(B3),NONBLANK(C3)).