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

 

[Pedro's PhD]

How did you obtain 6.43 for Set 2?

Set 2: (8+2+6+9+9+6+5)/7

The 8,2,6 are the three values below the max, and the 9,6,5 are the three above the max.

 

[Pedro's PhD]

aaalviny,

Thanks for you're time, but the first formula =MATCH(MAX($A$1031:$A$1331), $A$1031:$A$1331, 0) appears not to return the correct value.

If I input the max value into cell 1200, it returns '170' in Cell B1?

 

[Aladin Akyurek]

Set 2: (8+2+6+9+9+6+5)/7

The 8,2,6 are the three values below the max, and the 9,6,5 are the three above the max.

Thanks...

=AVERAGE(OFFSET(INDEX(A2:A9,MATCH(MAX(A2:A9),A2:A9,0)),-3,0,7))

If E2 = 3 (the parameter value)...

=AVERAGE(OFFSET(INDEX(A2:A9,MATCH(MAX(A2:A9),A2:A9,0)),-E2,0,2*E2+1))

 

[aaalviny]

Hi Pedro,

it's correct, because it is the 170th element in your data range $A$1031:$A$1331
(i.e. 1200-1031+1 = 170)

note: the 2nd formula (in B2) give the correct answer

Alvin

 

[aaalviny]

Thanks...

=AVERAGE(OFFSET(INDEX(A2:A9,MATCH(MAX(A2:A9),A2:A9,0)),-3,0,7))

If E2 = 3 (the parameter value)...

=AVERAGE(OFFSET(INDEX(A2:A9,MATCH(MAX(A2:A9),A2:A9,0)),-E2,0,2*E2+1))

do you think it will fail if the maximum is located at A2? (with E2=3)

note: similar problem as I stated before because OFFSET doesn't lock the intended data range
or, at least, using "OFFSET" won't show an error if the cells finally used are outside the original data range

 

[Pedro's PhD]

Hi Alvin,

Thanks for the clarification, works perfectly!

Thanks.... Pedro

 

[Piktro]

Edit: sorry, thought I was on the last page but I wasn't - someone else answered already.

 

[Pedro's PhD]

Hi Aladin,

This formula also works perfectly for me. My data for the current task will always be within the same data range so it's fine.

Many thanks... Pedro

 

[Aladin Akyurek]

Hi Aladin,

This formula also works perfectly for me. My data for the current task will always be within the same data range so it's fine.

Many thanks... Pedro

You are welcome.

 

[aaalviny]

Hi Pedro,

just a reminder that you may have to make sure either one of below
1. please make sure no one will enter data in range on or after A1332... or before A1031...
2. please make sure the max value won't be at the beginning / end of data (or that parameter "25" won't go too large)
(but actually, in my point of view, I don't think any people can be sure in the future that the maximum won't be located at the beginning / end of data)
also at least I think a comment should be added to the cells to state clear that the cells before the data range & after the data range should be empty

in order to let other users understand the spreadsheet easily

p.s. I also wonder why the data is put in such bottom rows (after 1000 rows of things..) it would be difficult for other users to find out