#div/0!

  • Thread starter Thread starter big jim
  • Start date Start date
B

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
 
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;;
 
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
 
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
 
I have the formula =average(h22:h33)

Jim

RagDyeR said:
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
Click to expand...
 
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?
 
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
 
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
 
Hi
then PLEASE post these formulas and also explain if these formulas do
return errors on its own?

--
Regards
Frank Kabel
Frankfurt, Germany

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

Jim S

Frank Kabel said:
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

big jim said:
I have the formula =average(h22:h33)

Jim

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

Regards,

RD
Click to expand...
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
Click to expand...
zero
and I
still want to see what the average was in the total column for
Click to expand...
the
first
10
months. The cells are formatted to show % and I notice that if
Click to expand...
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
Click to expand...
#div/0!.
Is
there a
way to force it to show the average if you only have data in
Click to expand...
H22
and
H23
etc?

Jim
Click to expand...
-
----
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
Click to expand...
Click to expand...
Click to expand...
 
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

big jim said:
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

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

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

Regards,

RD
Click to expand...
!
--------------------------------------------------------------------


I have the formula =average(h22:h33)

Jim

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

Regards,

RD
Click to expand...
Click to expand...
benefit !
Click to expand...
 
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
 
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
 
Back
Top