Variable Mean Source Range

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

I am trying to discern an average of values from variable source ranges. If
my data looks like this:

A B C D
E
1 01 5 0 0
2 01 0 7 0
AVERAGEIF(A1:D2,<>0)
3 02 4 0 0
4 02 0 11 0
5 02 0 0 2
AVERAGEIF(A3:D5,<>0)

The workbook I'm working with has over 65,000 rows, so how can I set
something up where the average formula is automatically generated for each
subsequent set of similar data (in this example, column A is my qualifying
criteria... I want the average of all data in a row where A=01, A=02, etc).
Thanks!
 
Put this in E1:

=IF(A1=A2,"",(SUMIF(A:A,A1,B:B)+SUMIF(A:A,A1,C:BC)+SUMIF(A:A,A1,D:D))/
(COUNTIF(A:A,A1)*3))

and copy down as far as required.

It assumes that your data in column A is sorted, as your example
implies.

Hope this helps.

Pete
 
Forgive me, the example I gave you was incorrect. There are two criteria to
determine the range, and the initial average included column A. However, the
data being averaged is located between C and XX (in this example, E).
 
Back
Top