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:

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 

doublej41

Board Regular
Joined
Mar 9, 2011
Messages
86
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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
 

doublej41

Board Regular
Joined
Mar 9, 2011
Messages
86
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,611
Messages
5,523,889
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top