# Averaging values from a list

#### doublej41

##### Board Regular
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

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

#### barry houdini

##### MrExcel MVP
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
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
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
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
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!

Replies
3
Views
84
Replies
10
Views
100
Replies
0
Views
29
Replies
2
Views
54
Replies
1
Views
43