how to average row averages

T

THE BOB

I have a spreadsheet with 2 sheets. I'm using a formula on page one to
average each row of page two (six columns). Then I have to average the
averages within the same formula. The number of rows of data on page two is
unlimited and I can't figure out how to average each row without loading a
formula for each row. I there a way to do this automatically? Here is a
sample of the formula I'm using for the first row which is row 12:

=AVERAGE(AVERAGE(SHEET2!AG$12,AL$12))
 
S

Shane Devenshire

The Average of an Average is the average of all the data. In other words you
are working too hard.

If you want the average of the average of the rows on sheet2 it is simply
the AVERAGE(Sheet2!A1:AL50)
 
T

T. Valko

If each row contains the same count of numbers then the average of averages
would just be the average of the entire range. If the rows might contain
differing counts of numbers then an average of averages may not be the same
as the average of the entire range.

......A.....B.....C..........D
1.............................Avg
2..11...54................32.5
3..58...7.....33.........32.66667
4..80..........8...........44
5..42...23...79.........48
6...........................................
7...............Avg.......39.29167

The average of the averages is 39.29167 but the average of the range is
39.5.

=AVERAGE(A2:C5) = 39.5

Array entered** :

=AVERAGE(SUBTOTAL(1,OFFSET(A2:C5,ROW(A2:C5)-ROW(A2),,1,3)))

= 39.29167

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
T

THE BOB

The problem with this is that each row may or may not have the same counts of
numbers to be averaged, so I wouldn't necessarily come up with the same
result as if I averaged each row individually and then averaged those
averages. I'm hoping there is an easy way to just average every row of the
spreadsheet. I'd appreciate any more suggestions.

Thanks,
Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top