Using averages wont calculate

[big jim]

I have a spreedsheet that caculates averages improperly and I dont know what
to do to correct it? I have a bar graph that compares last year with this
year but since the bottom row of this insert doesnt show the average there
is nothing showing up on the graph. The formula in the bottom row is
=average(f22::f33) but it wont show anything until the entire insert has
numbers in it. Is there a way to get it to show a running average at all
times?

Overall
shop 1 shop 2 shop 3 shop 4 average
Jan 97.4% 97.4% 100.0% 98.3%
Feb #DIV/0!
Mar #DIV/0!
Apr #DIV/0!
May #DIV/0!
June #DIV/0!
July #DIV/0!
Aug #DIV/0!
Sept #DIV/0!
Oct #DIV/0!
Nov #DIV/0!
Dec #DIV/0!
#DIV/0!

Thanks for your help
Jim

 

[Mark Graesser]

Big Jim
I have two options for you

First, you can supress the #DIV/0! errors by putting the formulas in your table inside an IF statement. The following example checks if the denominator is zero and returns a pseudo-blank if it is

=IF(B1=0,"",A1/B1

The AVERAGE function will then ingore this cell entirely

Second, you can use the following array entered formula to suppress the #DIV/0! within the average function

=AVERAGE(IF(ISERR(C4:C14),"",C4:C14)

After typing in the formula hit "control-shift-enter' instead of just enter. The formula should appear in the fiormula bar with { } around it. Update the range reference to yours

Good Luck
Mark Graesse
(e-mail address removed)

----- big jim wrote: ----

I have a spreedsheet that caculates averages improperly and I dont know wha
to do to correct it? I have a bar graph that compares last year with thi
year but since the bottom row of this insert doesnt show the average ther
is nothing showing up on the graph. The formula in the bottom row i
=average(f22::f33) but it wont show anything until the entire insert ha
numbers in it. Is there a way to get it to show a running average at al
times

Overal
shop 1 shop 2 shop 3 shop 4 averag
Jan 97.4% 97.4% 100.0% 98.3
Feb #DIV/0
Mar #DIV/0
Apr #DIV/0
May #DIV/0
June #DIV/0
July #DIV/0
Aug #DIV/0
Sept #DIV/0
Oct #DIV/0
Nov #DIV/0
Dec #DIV/0
#DIV/0

Thanks for your hel
Ji