Clear Cell If Function = 0

[Geno]

Hello,

I have a spreadsheet that contains averages and sums of columns, tracking
mileage and fuel costs by the month for 12 months. The first 3 months (12
separate Sheets) obviously have data in them: Jan ~ March, but the rest of
the sheets display [#DIV/0!] where data has yet to be entered.
I know it is something simple but I just can’t remember how to return
nothing in the cells where data isn’t present.

YTD sheet #13, cell I57 = Apr-06 I70 which contains this function [=AVERAGE
(I61:I69)]

I am thinking it is something like this but haven’t gotten it right so far:

[ =AVERAGE (I61:I69) IF<0=0 ]

I want the cells to display 0 or nothing if data has not yet been entered.

Thanks so much for your help!

[daddylonglegs]

I'd suggest either

=IF(COUNT(I61:I69),AVERAGE(I61:I69),"")

or

=SUM(I61:I69)/MAX(1,COUNT(I61:I69))

[Richard Buttrey]

On Sun, 12 Mar 2006 15:26:27 -0800, Geno
<[email protected]> wrote:

>Hello,
>
>I have a spreadsheet that contains averages and sums of columns, tracking
>mileage and fuel costs by the month for 12 months. The first 3 months (12
>separate Sheets) obviously have data in them: Jan ~ March, but the rest of
>the sheets display [#DIV/0!] where data has yet to be entered.
>I know it is something simple but I just can’t remember how to return
>nothing in the cells where data isn’t present.
>
>YTD sheet #13, cell I57 = Apr-06 I70 which contains this function [=AVERAGE
>(I61:I69)]
>
>I am thinking it is something like this but haven’t gotten it right so far:
>
>[ =AVERAGE (I61:I69) IF<0=0 ]
>
>I want the cells to display 0 or nothing if data has not yet been entered.
>
>Thanks so much for your help!

Just wrap the whole thing in an IF(ISERROR()) function. i.e.

IF(ISERROR(=AVERAGE (I61:I69)),0, AVERAGE (I61:I69))

Replace the 0 with "" if you wish to show Null or nothing.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Peo Sjoblom]

=IF(COUNT(I61:I69)=0,0,your_average_formula)

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Geno" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a spreadsheet that contains averages and sums of columns, tracking
> mileage and fuel costs by the month for 12 months. The first 3 months (12
> separate Sheets) obviously have data in them: Jan ~ March, but the rest of
> the sheets display [#DIV/0!] where data has yet to be entered.
> I know it is something simple but I just can’t remember how to return
> nothing in the cells where data isn’t present.
>
> YTD sheet #13, cell I57 = Apr-06 I70 which contains this function
> [=AVERAGE
> (I61:I69)]
>
> I am thinking it is something like this but haven’t gotten it right so
> far:
>
> [ =AVERAGE (I61:I69) IF<0=0 ]
>
> I want the cells to display 0 or nothing if data has not yet been entered.
>
> Thanks so much for your help!
>

[Geno]

The formula from daddylonglegs worked perfectly, Thank You!
The formula from Peo Sjoblom flashed my memory on how to correct the other
cell problems I was having.
My mileage for 2006 is ready to Rock n Roll, thank you so much for your
assistance! Sopranos are starting soon, it’s nice to be done!