Re: eliminating the 0 in DIV/0

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

When you calm down and are able to stop laughing, can we know if it worked
for you ... or not?
--

Regards,

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

"teacher1" <teacher1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:283BBDDF-78BF-4DCE-A1F6-EC9F5E8D02E7@xxxxxxxxxxxxxxxx
Ragdyer -disregard last message sorry but i am still laughing at my self

"teacher1" wrote:

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 month's 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
month's 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" <teacher1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:67607BA5-8FD1-4D26-B841-ED7C7C35F5BA@xxxxxxxxxxxxxxxx
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?




.

Relevant Pages

  • Re: #DIV/0! Error-Another Twist, assistance please?
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... #DIV/0! ... Is there any way of using an IF statement to keep the cells blank, ... relevant data is input. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: #DIV/0! Error-Another Twist, assistance please?
    ... Microsoft Excel MVP ... #DIV/0! ... Is there any way of using an IF statement to keep the cells blank, ... relevant data is input. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Summing only cells that have data and ignoring cells that have #DIV/0! in them
    ... through Z feed formulas in columns AC through AL. Prior to putting ... because of course there's no data in the cells ... I want to sum the totals of some of the columns that in AC through AL, ... but of course if I only have data in rows 2-10, then the #DIV/0! ...
    (microsoft.public.excel)
  • Re: eliminating the 0 in DIV/0
    ... Please keep all correspondence within the NewsGroup, ... what I want to is get the average of a column that has a DIV/0 in the column. ... What I want to do is keep the formulas in the cells, ...
    (microsoft.public.excel.misc)
  • Re: Ignore error msgs in formula references
    ... div/0 error. ... > I have an Average formula that refers to cells linked to another spreadsheet. ... > The linked spreadhsheet gets updated each month, so that for months with no ... > I have found that you can use the IF function to display a string value in a ...
    (microsoft.public.excel.misc)