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

[BMPeers]

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

 

[pplstuff]

b3: =IF(AND(ISNUMBER(A3),ISNUMBER(A4),ISNUMBER(A5),ISNUMBER(A6)),AVERAGE(A3:A6),"")
^^Drop down
Then take the max and min.

 

[Robert Mika]

			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]

Thanks ppl,

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

 

[BMPeers]

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]

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]

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]

Sorry I do have hundreds of columns, each with different amounts of values in...
That's why I need one dynamic formula.