Find MAX in range and average the values in adjacent 25 cells

[Pedro's PhD]

Hey everyone,

I have data in cell range A1031:A1331. I need to find the maximum value within this range and then compute the combined average from the 25 cells before the maximum value and the 25 cells after the maximum value.

For example: If the MAX was in cell A1150, then I would need the average of A1125:A1175

Thanks..... Pedro

 

[p45cal]

What if there are several cells with the maximum value?

 

[aaalviny]

Hey everyone,

I have data in cell range A1031:A1331. I need to find the maximum value within this range and then compute the combined average from the 25 cells before the maximum value and the 25 cells after the maximum value.

For example: If the MAX was in cell A1150, then I would need the average of A1125:A1175

Thanks..... Pedro

1. lookup the position of max value in the data
cell B1: =MATCH(MAX($A$1031:$A$1331), $A$1031:$A$1331, 0)
2. find the average
cell B2: =AVERAGE(INDEX($A$1031:$A$1331, $B$1-25):INDEX($A$1031:$A$1331, $B$1+25))

 

[p45cal]

For the average:
=AVERAGE(OFFSET(A1031,MATCH(MAX(A1031:A1331),A1031:A1331,0)-26,0,51))
which will assume the max to be used is the first instance.

For the max:
MAX(A1031:A1331)

 

[Aladin Akyurek]

Hey everyone,

I have data in cell range A1031:A1331. I need to find the maximum value within this range and then compute the combined average from the 25 cells before the maximum value and the 25 cells after the maximum value.

For example: If the MAX was in cell A1150, then I would need the average of A1125:A1175

Thanks..... Pedro

Let's replace 25 with 3. What is expected outcome for the following sets:

Set 1:

3
7
2
3
4

Set 2:

8
2
6
9
9
6
5
2

 

[Pedro's PhD]

What if there are several cells with the maximum value?

If there are several cells with the maximum value, then the first maximum value should be used.

 

[Pedro's PhD]

Hi Aladin,

In the data which I am using, there will never be a maximum value within the first 25 cells.

So in your example Set 1: The max of seven, so below (just 3), and above (2,3,4) would be averaged alongside the max value to give 3.8

In Set 2: Max of 9 so below (6,2,8) and above (9,6,5) would be averaged alongside the max to give 6.43

Thanks

 

[aaalviny]

For the average:
=AVERAGE(OFFSET(A1031,MATCH(MAX(A1031:A1331),A1031:A1331,0)-26,0,51))
which will assume the max to be used is the first instance.

For the max:
MAX(A1031:A1331)

this will not show an error if the maximum value is at the end of data
the reason it happens is due to the volatile range property of "OFFSET", which cannot guarantee where the final cell would be located

 

[aaalviny]

Let's replace 25 with 3. What is expected outcome for the following sets:

Set 1:

3
7
2
3
4

Set 2:

8
2
6
9
9
6
5
2

Thanks for your post, I originally just intended to let Pedro deal with the two cases you mentioned above.

So in that case I would modify my solution below

1. lookup the position of max value in the data
cell B1: =MATCH(MAX($A$1031:$A$1331), $A$1031:$A$1331, 0)
2. find the average
cell B2: =AVERAGE(INDEX($A$1031:$A$1331, MAX(1, $B$1-25)):INDEX($A$1031:$A$1331, MIN($B$1+25, COUNTA($A$1031:$A$1331))))

Pedro,
If I were you, I would also take out formulas segments from the formula above for a clearer presentation (e.g. MAX(1, $B$1-25) & MIN($B$1+25) & COUNTA())
and on the other hand, I would also take the 25 out as a parameter defined in cell, say, B3

 

[Aladin Akyurek]

Hi Aladin,

In the data which I am using, there will never be a maximum value within the first 25 cells.

So in your example Set 1: The max of seven, so below (just 3), and above (2,3,4) would be averaged alongside the max value to give 3.8

In Set 2: Max of 9 so below (6,2,8) and above (9,6,5) would be averaged alongside the max to give 6.43

Thanks

How did you obtain 6.43 for Set 2?