How to get a formula result zero as blank cell

E

Excelerate-nl

Dear Excel experts,

How can I make the outcome of a formula (eg. =A1*A2) result in a blank cell
if either Cell A1 or Cell A2 is blank (no data typed in the cell)?
If either Cell A1 or A2 is actually zero it should not result in a blank Cell.

I need to define the outcome of the folrmula as blank, because I want to
take the average of a range of rhese formulas. If some data have not been
entered, they should be ignored in the average and not counted as 0.

Hope you can give me a clue,

Jan Bart
 
S

Sloth

=IF(OR(A1="",A2=""),"",A1*A2)

Average, sum, and count functions will ignore cells with a result of "".
COUNTA will still count cells with "".
 
E

Excelerate-nl

Thanks Steve.

I already thought of this solution, but it results is 34*365 formulas
(besides lots of other formulas) of this type which may overload the
processor.
So I am still hoping for another clue.

regards,

Jan Bart
 
D

Dave Peterson

One more:

=if(count(a1,a2)<2,"",a1*a2)



Excelerate-nl said:
Dear Excel experts,

How can I make the outcome of a formula (eg. =A1*A2) result in a blank cell
if either Cell A1 or Cell A2 is blank (no data typed in the cell)?
If either Cell A1 or A2 is actually zero it should not result in a blank Cell.

I need to define the outcome of the folrmula as blank, because I want to
take the average of a range of rhese formulas. If some data have not been
entered, they should be ignored in the average and not counted as 0.

Hope you can give me a clue,

Jan Bart
Click to expand...
 
Top