DIV/0 error

[Bruce D.]

Hi all,

I need help on a formula. I have a cell Z3 which is YTD. It calcuates the
average from cells A3:Y3 (months of the year). So far so good. The cells
A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is
calcuating all of the monthly averages which is working ok. The problem is if
I have not entered any weekly data for the future months I am getting the
#DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the
help solutions. But it looks like I can't use their solution because I am
using a range of cells. This is what I attempted:
=IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error.

Any ideas?? Any information is greatly appreciated!!!!!
--
Bruce DiPaola

[Niek Otten]

Hi Bruce,

=IF(ISERROR(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten

"Bruce D." <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I need help on a formula. I have a cell Z3 which is YTD. It calcuates the
> average from cells A3:Y3 (months of the year). So far so good. The cells
> A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is
> calcuating all of the monthly averages which is working ok. The problem is
> if
> I have not entered any weekly data for the future months I am getting the
> #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the
> help solutions. But it looks like I can't use their solution because I am
> using a range of cells. This is what I attempted:
> =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error.
>
> Any ideas?? Any information is greatly appreciated!!!!!
> --
> Bruce DiPaola

[Dave O]

This solution may work for you- since you're using a simple average you
can change the formula to
SUMIF(range, "<>0", range) / COUNTIF(range,"<>0")

This sums non-zero values and divides by the number of non-zero values.
Use this ONLY on a continuous range of numbers, because
COUNTIF(range,"<>0") will count blank cells.

[Randy Davis]

I think the iserror formula will be simpler if you already have the spreadsheet set up. Just make sure that A3 - Y3 have =if(iserror(4 week average formula)," ",4 week average formula).

This solution may work for you- since you're using a simple average you
can change the formula to
SUMIF(range, "<>0", range) / COUNTIF(range,"<>0")

This sums non-zero values and divides by the number of non-zero values.
Use this ONLY on a continuous range of numbers, because
COUNTIF(range,"<>0") will count blank cells.

[Bruce D.]

Hi Niek,

I entered =IF(ISERROR(AVERAGE(C3:Y3),"",AVERAGE(C3:Y3) and the cursor moves
to the "" quotes.
--
Bruce DiPaola


"Niek Otten" wrote:

> Hi Bruce,
>
> =IF(ISERROR(YourFormula),"",YourFormula)
>
> --
> Kind regards,
>
> Niek Otten
>
> "Bruce D." <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > I need help on a formula. I have a cell Z3 which is YTD. It calcuates the
> > average from cells A3:Y3 (months of the year). So far so good. The cells
> > A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is
> > calcuating all of the monthly averages which is working ok. The problem is
> > if
> > I have not entered any weekly data for the future months I am getting the
> > #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the
> > help solutions. But it looks like I can't use their solution because I am
> > using a range of cells. This is what I attempted:
> > =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error.
> >
> > Any ideas?? Any information is greatly appreciated!!!!!
> > --
> > Bruce DiPaola
>
>
>

[B. R.Ramachandran]

Hi,

In A3 enter the formula,

=IF(ISERROR(AVERAGE(4-week range for A3)),"",AVERAGE(4-week range for A3))

where "4-week range for A3" is the range of cells containing tthe data for
calculating the average in cell A3. Drag the formula across to Y3.

The formula in Z3 is =AVERAGE(A3:Y3). and it should behave properly.

Regards,
B. R. Ramachandran


In fact

"Bruce D." wrote:

> Hi all,
>
> I need help on a formula. I have a cell Z3 which is YTD. It calcuates the
> average from cells A3:Y3 (months of the year). So far so good. The cells
> A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is
> calcuating all of the monthly averages which is working ok. The problem is if
> I have not entered any weekly data for the future months I am getting the
> #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the
> help solutions. But it looks like I can't use their solution because I am
> using a range of cells. This is what I attempted:
> =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error.
>
> Any ideas?? Any information is greatly appreciated!!!!!
> --
> Bruce DiPaola

[Bruce D.]

Hi B.R.

I tried your solution and it works perfectly. You are the man...
Many thanks....
--
Bruce DiPaola


"B. R.Ramachandran" wrote:

> Hi,
>
> In A3 enter the formula,
>
> =IF(ISERROR(AVERAGE(4-week range for A3)),"",AVERAGE(4-week range for A3))
>
> where "4-week range for A3" is the range of cells containing tthe data for
> calculating the average in cell A3. Drag the formula across to Y3.
>
> The formula in Z3 is =AVERAGE(A3:Y3). and it should behave properly.
>
> Regards,
> B. R. Ramachandran
>
>
> In fact
>
> "Bruce D." wrote:
>
> > Hi all,
> >
> > I need help on a formula. I have a cell Z3 which is YTD. It calcuates the
> > average from cells A3:Y3 (months of the year). So far so good. The cells
> > A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3 is
> > calcuating all of the monthly averages which is working ok. The problem is if
> > I have not entered any weekly data for the future months I am getting the
> > #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using the
> > help solutions. But it looks like I can't use their solution because I am
> > using a range of cells. This is what I attempted:
> > =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error.
> >
> > Any ideas?? Any information is greatly appreciated!!!!!
> > --
> > Bruce DiPaola

[Niek Otten]

Sorry, I missed the final quote

--
Kind regards,

Niek Otten

"Bruce D." <[email protected]> wrote in message
news:[email protected]...
> Hi Niek,
>
> I entered =IF(ISERROR(AVERAGE(C3:Y3),"",AVERAGE(C3:Y3) and the cursor
> moves
> to the "" quotes.
> --
> Bruce DiPaola
>
>
> "Niek Otten" wrote:
>
>> Hi Bruce,
>>
>> =IF(ISERROR(YourFormula),"",YourFormula)
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "Bruce D." <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi all,
>> >
>> > I need help on a formula. I have a cell Z3 which is YTD. It calcuates
>> > the
>> > average from cells A3:Y3 (months of the year). So far so good. The
>> > cells
>> > A3:Y3 contain average totals from the 4 weeks of the month. So cell Z3
>> > is
>> > calcuating all of the monthly averages which is working ok. The problem
>> > is
>> > if
>> > I have not entered any weekly data for the future months I am getting
>> > the
>> > #DIV/0 error and the YTD Z3 cell will not calcuate. I have tried using
>> > the
>> > help solutions. But it looks like I can't use their solution because I
>> > am
>> > using a range of cells. This is what I attempted:
>> > =IF(A3:Y3="#DIV/0","",AVERAGE (A3:Y3)). I then get a value error.
>> >
>> > Any ideas?? Any information is greatly appreciated!!!!!
>> > --
>> > Bruce DiPaola
>>
>>
>>