How do I get the average of entries in a group of columns?

[Guest]

I have a set of 4 columns of data that are all the result of a different
division formula corresponding to each column. Many of the entries are a
#DIV/0! entry.

I would like to take a single average of all the actual numeric entries in
the entire set of columns that were greater than zero (0) and less than ten
(10) in value. How would you suggest I proceed?

 

[Guest]

Hi Motown Mick,

I would start by using iserror with your division formula. I.e.
=IF(ISERROR(A1/B1)=TRUE, "", A1/B1)

That way you won't see any #DIV/0! errors.

hth

BigPig

 

[Guest]

Yes I'd get rid of the div errors as suggested. Here are two ways of doing
the average.

=(SUMIF(A10:A16,">0",A10:A16)-SUMIF(A10:A16,">10",A10:A16))/(COUNTIF(A10:A16,">0")-COUNTIF(A10:A16,">10"))

and

=SUMPRODUCT(--(A10:A16>0),--(A10:A16<10)*(A10:A16))/SUMPRODUCT(--(A10:A16>0),--(A10:A16<10))

Watch the wrapping!

The sumif/countif will work if there is a #div error in the range, the
sumproduct will not, but your are going to get rid of them aren't you?

Regards
Peter

 

[Guest]

Thank you, that got rid of the #DIV/0! errors.
-Mick

 

[Guest]

Dear Billy,

Thank you for your reply. I successfully got rid of the div errors. But
before I try either of these methods, I would like to know what A10:A16
represents. Is it, as I suspect, simply an arbitrary range you inserted just
for an example? Or is it part of the command structure? I am just thrown
off a little, because it is a larger range consisting of every row in 4
columns that I wish to take the average of.

Rather than writing in a special range segment of the column(s) when I
compose the formula, could I simply demark all 4 columns at the top of the
columns indicator bar, and continue with the rest of the formula?

Mick

 

[Guest]

Dear Peter:

I didn't hear from you over the weekend, so I decided to play with the
formulas you gave me, based on my intuition of what I ought to put in place
of A10:A16 (see my post of 7/19).

Even though I successfully cleared the field of the div errors, the
sumproduct method only gave me a #VALUE! entry in the cell.

The sumif/countif method seems to have worked on the very first try, because
I got a number that seemed right, and continued to get numbers that seemed
right when I applied it to a couple of other data fields.

I suppose a good way of checking would be to try both methods, and see if
they produce the same result. Are you sure that once you clear the field of
the div errors, the sumproduct method ought to work? Are you sure you typed
the correct formula for the sumproduct method in your post of 7/18?

Mick

 

[Guest]

Dear Peter:

I checked the sumif/countif method by taking a very small and manageable set
of data I had already computed the answer for, and doing copy>paste
special>values for each entry into a separate column, and then doing average
the conventional way, and the answer matched. So the sumif/countif method
definitely worked! Thanks for your help!

I'm still a little troubled why the sumproduct method didn't work, but
that's ok, I've gotten the results I need.

Best wishes,

Mick