Try something like this:

=SUMIF(K2:K120,"<#DIV/0!")/COUNT(K2:K120)
--
HTH,

RD

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

"teacher1" wrote in message
...
Hi Ragdyer! Thank you for taking the time to provide me with an answer!
I
put the formula you gave me in and it did indeed give me the average, but
what I want to is get the average of a column that has a DIV/0 in the
column.
For example:

I am trying to get the running average as each months figures are entered
in another column. All of the below figures represent the total figures
from the other columns, but the 5th cell down represents unknown results.

100
89
90
79
#DIV/0!

#DIV/0!

What I want to do is keep the formulas in the cells, and even though there
is a DIV/0 in one of the cells it will give me average (here it would be
89.5
or the average of the first four numbers and not counting that cell with
the
DIV/0 or unknown resluts. And, later, when the data is entered with that
months figures (assuming that figure is 65) it would give me the average
of
all (5) cells ( which would then be 85 as the average) Many Thanks again
for
your help!!


"Ragdyer" wrote:

I think you're looking to average across 4 columns,
BUT, if some of the columns are empty, you want to average *whatever*
columns you have values in.

If I interpreted that correctly, try this *array* formula in say E1, with
your data in A1 to D1:

=AVERAGE(A1:INDEX(A1:D1,SMALL(COLUMN(A1:D1)*(A1:D1 <""),4)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

--
HTH,

RD

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

"teacher1" wrote in message
...
I am a very basic XL user, but I work with spread sheets using the
simple
AVG
formula. Is there any way to by pass the "0" in DIV/0? in other
words, I
am
using 4 columns of numbers that were each averaged. There is another
column
with the average of the 4 separate columns, but I get the "DIV/0"
unless
all
4 of the other colums have a base larger than "0". In other words do I
have
to wait until all 4 coulms are completed before I can determine the on
going
average, or is there a formula or way to get the averages of the
columns
that
do not have a zero base and not have to wait until all 4 colums have a
base
larger than zero?