eliminating the 0 in DIV/0

[Guest]

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?

 

[curlydave]

what is the formula you are using right now?

 

[Bob Phillips]

Maybe try something like

=IF(COUNT(A1:A10)=0,"",AVERAGE(A1:A10))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Ragdyer]

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(A11,SMALL(COLUMN(A11)*(A11<>""),4)))

 

[Guest]

avg A B C D
1 91 ****** 91 91 91 91
2 89 89 89 89 89
3 78 78 78 78 78
4 65 65 65 65 65
5 77 77 77 77 77
6 68 68 68 68 68
7 86 86 86 86 86
8 89 89 89 89 89
9 95 95 95 95 95
10 #DIV/0! #DIV/0!
11 #DIV/0! #DIV/0!
12 #DIV/0! #DIV/0!

AVG #DIV/0! (THIS IS A MONTHLY AVERAGE)

****** = SUM OF COLUMN A WHICH IS THE AVERAGE OF COLUMNS B TO D

Can I get the current monthly average without having to post all of the
months results, that is, the average without the months that have "DIV/0"
which would be 82?
Many thanks for your help!!

 

[Guest]

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]

Try something like this:

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

 

[Guest]

Ragdyer - Thanks again, but I am not quiet sure what you mean by "click to
show or h ide the original message or reply test" Is this a format, options
or right click function. Sorry to ask for what appears to be simple answer,
but I am slighly on the XL gray matter (dumb is the politically incorrect
word) challenged. Thanks again!

 

[Guest]

Ragdyer -disregard last message sorry but i am still laughing at my self

 

[RagDyeR]

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

 

[Guest]

Unfortunately, it did not work. I only got a "VALUE" response. I think it
might be best if I start over with my question. Look at the following
example of a worksheet:
A C D E F G
H I
1 YRY AVG GRADES
GRD AVG
2 1-6WK 73.0 1-6WK 77 65 77
73
3 2-6WK 75.3 2-6WK 77 73 76 75.3
4 3-6WK 72.7 3-6WK 68 71 79 72.7
5 4-6WK 92.7 4-6WK 99 92 87 92.7
6 5-6WK DIV/0 5-6WK (BLANK CELLS)
DIV/0
7 6-6WK DIV/0 6-6WK (BLANK CELLS)
DIV/0
8
9Yrly AvG #DIV/0!

10 The average should be :78.4 through date in cell C 9 but will only get
"DIV/0" until data is entered in at least cell E 6 and /or E 7 to generate
the averages in I 6 and I 7 While this data will not be available until
later in the year, I nonetheless, need to keep a current average of those
that have been posted through the current date.
Now, considering the above example, is there a formula that will allow me to
obtain the running year to date averages in cell 9 without considering the
cells in C 6 (DIV/0) and/or C7: that is, only show the average for those
cells C2 -C5 and as soon as the data is entered in at least cells F6 and F7,
then it will automatically show these results when they are entered?

Again, I appreciate the time you and the group has taken to help me!

 

[Guest]

Sorry, the worksheet did not come out the way I posted it!