Averaging Blank Cells

J

John Calder

Hi

In cell R88 I have the following formula

=AVERAGE(R76:R87)

It works fine as soon as I enter data in the range but when there is no data
in the range it returns a #DIV/0! error.

This looks unsightly on the sheet and was wondering if there was a way that
I could surpress this error when there is no data entered.

Thanks

John
 
P

Pecoflyer

Hello

try =AVERAGE(if(R76:R87)<>0,r76:r87,false) and enter as an arry formul
with Ctrl+Shift+Enter

HTH

Hi

In cell R88 I have the following formula

=AVERAGE(R76:R87)

It works fine as soon as I enter data in the range but when there is n
data
in the range it returns a #DIV/0! error.

This looks unsightly on the sheet and was wondering if there was a wa
that
I could surpress this error when there is no data entered.

Thanks

Joh

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
J

Jarek Kujawa

try

=AVERAGE(IF(R76:R87,R76:R87,))

CTRL+SHIFT+ENTER this formula as it is in array formula
 
D

Dave Peterson

Check for some numbers first:

=if(count(r76:r87)=0,"no numbers",average(r76:r87))
 

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

Top