# #div/0 errors and blank cells

G

#### Guest

Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean

Ad

J

#### JE McGimpsey

One way:

=IF(COUNT(A1:A31)>0,AVERAGE(A1:A31),0)

P

#### Pete_UK

Try this:

=IF(COUNT(A1:AE1)=0,0,AVERAGE(A1:AE1))

Hope this helps.

Pete

G

#### Guest

=if(count(a1:a5)=0,"",average(a1:a5)).
This will first check if there are any numbers in the range. If not, leave
the result blank; if so, average those numbers.
--Bruce

G

#### Guest

spot on - works a treat guess i was missing the most obvious way

thanks again

D

#### Dave Thomas

If your data is in cells A1 through AE1, 31 cells, then you could use:
=IF(ISERROR(AVERAGE(A1:AE1)),0,AVERAGE(A1:AE1))
The ISERROR function will catch any kind of error, not just division by zero
, #DIV/0.

Ad

R

#### Rick Rothstein \(MVP - VB\)

Are you answering old messages from old threads? Your last two postings seem
to be answering questions from back on May 1st and May 2nd. Is that how your
newsreader is showing them to you?

Rick

Ad

T

#### T. Valko

Another way:

=IF(COUNT(A1:AE1),AVERAGE(A1:AE1),0)

## 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.