formulas that may sometimes contain empty cells

C

chrisnsmith

How do you get a formula that contains empty cells to ingnore the formula and
return a value of 0 (zero)
 
G

Gary''s Student

Say we are adding A1 thru A10, but if any are blank, report 0 instead:

=IF(COUNTBLANK(A1:A10)>0,0,SUM(A1:A10))

So you will see 0 until all the data is filled in.
 
S

Shane Devenshire

Hi,

This really depends on the formula. In general cells that are empty are
treated as 0. For example =SUM(A1:A10) returns 0 if all the cells are empty.
On the other hand

=AVERAGE(A1:A10)

returns DIV/0 if all the cells are empty. In that case you could use
=AVERAGEIF(A1:A10,"<>") in 2007
or in 2003
IF(COUNT(A1:A10)>0,AVERAGE(A1:A10),0)

We need to see your formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Newbie Help - IF Yes No Function 0
How to summarize copied data 2
Copy addition formula of non-consecutive horizontal cells 2
Excel equation question 1
Hide columns based on row values 2
Count Number of Unique Cells That Don't Have Certain Characters 0
if zero, then return blank/empty 8
Use TODAY() function as the criteria in a SUMIF formula 1

Top