Find the Max and Min of an Average Subset of a Range

BMPeers

New Member
Joined
Nov 5, 2004
Messages
36
Dear Excel Gurus,

Firstly apologies for the terrible title of this post, I didn't know how to quickly sumarise what I am trying to achieve. I have been working on this for hours and finally decided I needed help.

I have a list of numbers, typically around 20 (in lets say A3:A22). What is important to me is an average of the four entries next to each other, so A3:A6, A4:A7, A5:A7... etc... to A19:A22.

I'd like to find the maximum and minimum of the average subsets inside the overall range.
Ideally I'd like it so the total size of the range could change, normally within the limits of around 10 to 30.

Many Thanks in advance for any help
Thanks Brian
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
b3: =IF(AND(ISNUMBER(A3),ISNUMBER(A4),ISNUMBER(A5),ISNUMBER(A6)),AVERAGE(A3:A6),"")
^^Drop down
Then take the max and min.
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
A</SPAN>
17.5</SPAN>
B</SPAN>
15</SPAN>
C</SPAN>
5.25</SPAN>
A</SPAN>
10</SPAN>
D</SPAN>
6</SPAN>
A</SPAN>
20</SPAN>
A</SPAN>
30</SPAN>
Min</SPAN>
5.25</SPAN>
A</SPAN>
10</SPAN>
Max</SPAN>
17.5</SPAN>
B</SPAN>
20</SPAN>
B</SPAN>
20</SPAN>
B</SPAN>
10</SPAN>
B</SPAN>
10</SPAN>
C</SPAN>
5</SPAN>
C</SPAN>
5</SPAN>
C</SPAN>
5</SPAN>
C</SPAN>
6</SPAN>
D</SPAN>
6</SPAN>
D</SPAN>
6</SPAN>
D</SPAN>
6</SPAN>
D</SPAN>
6</SPAN>
E</SPAN>
6</SPAN>
E</SPAN>
4</SPAN>
E</SPAN>
4</SPAN>
E</SPAN>
4</SPAN>

<TBODY>
</TBODY>

Thw work around
Add letters every for rows then use in E1 copy down to E4
=AVERAGE(IF($A$4:$A$23=D1,$B$4:$B$23))
confirm Control+Shift+enter
Then use MAX and MIN in E6 and E7
 

BMPeers

New Member
Joined
Nov 5, 2004
Messages
36

ADVERTISEMENT

As a quick note I had found and was using:

=SUMPRODUCT(MAX(A3:A22+OFFSET(A3:A22,1,0)+OFFSET(A3:A22,2,0)+OFFSET(A3:A22,3,0)+OFFSET(A3:A22,4,0)))/5

BUT this doesn't work with ranges which are different sizes...
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
Thanks ppl,

But I'm looking for this to be calculated in one cell...!

Hmmm... Didn't realize one cell. Makes it a bit trickier. I'll try and post back if I think of something in the next few mins.
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
If your range is only thirty cells, why not take the time to set it up longways once?

=max(average(a3:a6),average(a4:a7),average()...)

If you know your list will always be this short length, it would seem like you should just do a formula that requires some typing. You can spend time typing, or thinking lol. You could piece it together rather quickly this way (even though it's not a glamorous formula). I could see having a stronger function if you have 1,000's of cells, but 30 isn't that bad.
 

BMPeers

New Member
Joined
Nov 5, 2004
Messages
36
Sorry I do have hundreds of columns, each with different amounts of values in...
That's why I need one dynamic formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

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
Top