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))

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"THE BOB" wrote:

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))

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.

--
Biff
Microsoft Excel MVP


"THE BOB" <THE wrote in message
...
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))

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

"Shane Devenshire" wrote:

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"THE BOB" wrote:

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))