Averaging values from a list

doublej41

Board Regular
Joined
Mar 9, 2011
Messages
86
Hi All,

I am working with some long lists of test data. Is there any way I can average the 10 maximum and 10 minimum values automatically? At the moment I am having to go through the lists and pick them out myself.

Also, is there a way I can automatically split the data into blocks and average them? For example, if I have 100 entries, is it possible to average the first 10 entries, the second 10 entries... and so on so that I have 10 values?

As always, any help is greatly appreciated.

Thanks.
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
To average the 10 largest values try

=AVERAGE(LARGE(range,{1,2,3,4,5,6,7,8,9,10}))

for the average of the smallest 10 replace LARGE with SMALL
 
Upvote 0
Hi,

Assuming you data in A1:A100, for the averages 10 by 10 maybe this
=AVERAGE(OFFSET($A$1:$A$10,(ROWS($1:1)-1)*10,0))
copy down

HTH

M.
 
Upvote 0
Thank you for your replies.

Barry the formula you have posted works perfectly, thank you.

Marcelo when I enter the formula you have posted Excel tells me there is an error in the formula and highlights the $A$10. I wouldn't know where to begin in altering it, can you see why it might be giving an error?
 
Upvote 0
Be aware that OFFSET is a volatile function so can slow your sheet if used a lot. Here is a non-volatile way to average your blocks of 10. In my example I just have the numbers 1 to 100 in A1:A100 and the formula in B1 is copied down 10 rows.

If you don't know how many you will have, then a modified solution could be found, just post back with any more info you have about the possible number of rows.

Excel Workbook
AB
115.5
2215.5
3325.5
4435.5
5545.5
6655.5
7765.5
8875.5
9985.5
101095.5
1111
1212
1313
1414
1515
1616
1717
1818
1919
2020
2121
Average blocks
 
Upvote 0
Thanks Peter, working a treat.

It will always be known how many rows there will be so this formula is fine.

Thanks for all the help!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top