#div/0!

[big jim]

I am trying to get an average score for each month of the year and I have a
spreedsheet where the total is the following formula =AVERAGE(H22:H33) But
until each month ot the year is completed it only shows #div/0!. Is there a
way to force it to show the average if you only have data in H22 and H23
etc?

Jim

 

[Frank Kabel]

Hi
=IF(COUNT(H22:H33),AVERAGE(H22:H33),"")

 

[Aladin Akyurek]

How about:

=SUM(H22:H33)/MAX(1,COUNT(H22:H33))

This will still give you a #DIV/0! error if all entries in H22:H3
consists of real 0's.

Apply, if you so wish, the following custom format to the formul
cell:

0;0;;

 

[big jim]

Neither formula suggestion worked it still shows #DIV/0! but at this point
the data in h32 and h33 which is month 11 and 12 are also at zero and I
still want to see what the average was in the total column for the first 10
months. The cells are formatted to show % and I notice that if I change it
the format to number the #DIV/0! goes away and I can see the average but am
I not going to be able to show the results in %?

Jim

 

[RagDyeR]

Exactly what formula do you have in H22:H33?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Neither formula suggestion worked it still shows #DIV/0! but at this point
the data in h32 and h33 which is month 11 and 12 are also at zero and I
still want to see what the average was in the total column for the first 10
months. The cells are formatted to show % and I notice that if I change it
the format to number the #DIV/0! goes away and I can see the average but am
I not going to be able to show the results in %?

Jim

 

[big jim]

I have the formula =average(h22:h33)

Jim

Exactly what formula do you have in H22:H33?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Neither formula suggestion worked it still shows #DIV/0! but at this
point
the data in h32 and h33 which is month 11 and 12 are also at zero and I
still want to see what the average was in the total column for the first
10
months. The cells are formatted to show % and I notice that if I change
it
the format to number the #DIV/0! goes away and I can see the average but
am
I not going to be able to show the results in %?

Jim

 

[Frank Kabel]

Hi
but then either Aladin's or my formula should have worked. Or do you
also have formulas in the cells H22 to H33 (which may for itself result
in a #DIV/0 error?

 

[big jim]

Yes I do have formulas in H22 thru H33 that are pulling data from somewhere
else.

Jim S

 

[RagDyeR]

The question was "What formula is in H22:H33"!

I'm sure you don't have "=average(h22:h33)"
*IN* H22 !!!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


I have the formula =average(h22:h33)

Jim

 

[big jim]

In H22 I have the formula
=SUM(ESP2!F22+LV!F22+LA!F22+RATON!F22+SF1!F22+SF2!F22+SF3!F22+TAOS!F22)/8
which is pulling info from several different pages of a workbook that are
also in a percentage format.

Jim

 

[Frank Kabel]

Hi
then PLEASE post these formulas and also explain if these formulas do
return errors on its own?

--
Regards
Frank Kabel
Frankfurt, Germany

Yes I do have formulas in H22 thru H33 that are pulling data from somewhere
else.

Jim S

Hi
but then either Aladin's or my formula should have worked. Or do you
also have formulas in the cells H22 to H33 (which may for itself result
in a #DIV/0 error?

--
Regards
Frank Kabel
Frankfurt, Germany

I have the formula =average(h22:h33)

Jim

Exactly what formula do you have in H22:H33?
--

Regards,

RD

benefit
!

--------------------------------------------------------------------

Neither formula suggestion worked it still shows #DIV/0! but at this
point
the data in h32 and h33 which is month 11 and 12 are also at

zero
and I

still want to see what the average was in the total column for

the
first

10
months. The cells are formatted to show % and I notice that if

I
change

it
the format to number the #DIV/0! goes away and I can see the average but
am
I not going to be able to show the results in %?

Jim

message
How about:

=SUM(H22:H33)/MAX(1,COUNT(H22:H33))

This will still give you a #DIV/0! error if all entries in H22:H33
consists of real 0's.

Apply, if you so wish, the following custom format to the formula
cell:

0;0;;

big jim Wrote:
I am trying to get an average score for each month of the year and I
have a
spreedsheet where the total is the following formula =AVERAGE(H22:H33)
But
until each month ot the year is completed it only shows

#div/0!.
Is

there a
way to force it to show the average if you only have data in

H22
and

H23
etc?

Jim

-
----

Aladin Akyurek's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4165
View this thread:
http://www.excelforum.com/showthread.php?threadid=271834

 

[Frank Kabel]

Hi
do these formulas return an error?
also note: no need for the SUM formula in your case. If these
worksheets are one after the other try the formula
=SUM(ESP2:TAOS!F22)/8

this should prevent a #VALUE error if these cells contain text values

--
Regards
Frank Kabel
Frankfurt, Germany

In H22 I have the formula
=SUM(ESP2!F22+LV!F22+LA!F22+RATON!F22+SF1!F22+SF2!F22+SF3!F22+TAOS!F22)
/8
which is pulling info from several different pages of a workbook that are
also in a percentage format.

Jim

The question was "What formula is in H22:H33"!

I'm sure you don't have "=average(h22:h33)"
*IN* H22 !!!
--

Regards,

RD

!

--------------------------------------------------------------------


I have the formula =average(h22:h33)

Jim

Exactly what formula do you have in H22:H33?
--

Regards,

RD

benefit !

 

[RagDyeR]

Assuming that you will *not* have negatives, try this formula:

=SUMIF(H22:H33,">0")/COUNTIF(H22:H33,">0")

As far as your formula to sum across your sheets, have a look at this old
post to enable you to make a shorter formula, similar to Frank's suggestion:

http://tinyurl.com/6gujb
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



In H22 I have the formula
=SUM(ESP2!F22+LV!F22+LA!F22+RATON!F22+SF1!F22+SF2!F22+SF3!F22+TAOS!F22)/8
which is pulling info from several different pages of a workbook that are
also in a percentage format.

Jim

 

[Jerry W. Lewis]

On the assumption that the formulas in the referenced range are
returning error values, try
=IF(COUNT(H22:H33),AVERAGE(IF(ISNUMBER(H22:H33),H22:H33)),"")
array entered (Ctrl+Shift+Enter)

Jerry