How to return an empty cell?

Posted by Mike Kleimeyer on January 31, 2001 12:28 PM

I want an IF statement to return an empty cell not a "" (blank). The cell is read into a second equation and the "" cell is being seen as text I think, and returns an error as a result when the 2nd equation reads it. Help and thanks in advance!

Posted by Mark W. on January 31, 2001 12:30 PM

Excel doesn't support a null value. What's the
2nd equation that produces an error?

Posted by Mike Kleimeyer on January 31, 2001 2:59 PM

=IF(F8+H8+J8+L8+N8=0,0,(G8+I8+K8+M8+O8)/(IF(G8>0,1,0)+IF(I8>0,1,0)+IF(K8>0,1,0)+IF(M8>0,1,0)+IF(O8>0,1,0)))

There are times when any or all of F8+H8+J8+L8+N8
could have a "" blank in them

Thanks

Posted by Mark W. on January 31, 2001 4:17 PM

Mike, the SUM() function ignores text. Try this:

=IF(SUM(F8,H8,J8,L8,N8)=0,0,SUM(G8,I8,K8,M8,O8)/(IF(G8>0,1,0)+IF(I8>0,1,0)+IF(K8>0,1,0)+IF(M8>0,1,0)+IF(O8>0,1,0)))

BTW, you can simply this formula in other ways too!
Try this:

=IF(SUM(F8,H8,J8,L8,N8),SUM(G8,I8,K8,M8,O8)/((G8>0)+(I8>0)+(K8>0)+(M8>0)+(O8>0)),0)

Posted by Aladin Akyurek on January 31, 2001 4:50 PM

If you want the true zero values counted in the denominator, use

=IF(SUM(F8,H8,J8,L8,N8)>0,IF(SUM(G8,I8,K8,M8,O8)>0,SUM(G8,I8,K8,M8,O8)/COUNT(G8,I8,K8,M8,O8),0),0)

If not, use

=IF(SUM(F8,H8,J8,L8,N8)>0,IF(SUM(G8,I8,K8,M8,O8)>0,SUM(G8,I8,K8,M8,O8)/(COUNTIF(G8,">0")+COUNTIF(I8,">0")+COUNTIF(K8,">0")+COUNTIF(M8,">0")+COUNTIF(O8,">0")),0),0)

Aladin