Averaging Blank Cells
 

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

Averaging Blank Cells
 

Hello

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

HTH


John Calder;291643 Wrote:
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


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81481

Averaging Blank Cells
 

One way:
=IF(ISERROR(AVERAGE(R76:R87)),"",AVERAGE(R76:R87))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"John Calder" wrote:
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

Averaging Blank Cells
 

try

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

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


On 1 Kwi, 13:04, John Calder
wrote:
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

Averaging Blank Cells
 

Check for some numbers first:

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



John Calder wrote:

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

--

Dave Peterson