Stumped! Finding average after max?

[Crazysgt]

I am in the need of some help. What I have is a simple excel data set. 2 columns, Column A is Time of day in 24 hrs @ 15 min intervals, (A1:A96). Column B contains whole numbers, (4 digits with comma) that are entered at every corresponding 15 min interval in Column A. I am needing the Max of Column B, which is obviously easy. My issue is I am needing the average, starting from the Cell containing the Max and the next 7 consecutive cells. It doesn't matter to me if the formula needs to correspond with the Max, or if using the Time the Max is found and averaging the next 7-15 min cell data including the Max.

For hopefully some better insight:

What is happening is every 15 minutes, a reading is taken from a flow meter automatically via a program called XLR Reporter. The State Regulatory Agency over this program requires a 2 hour peak average for every 24 hrs, starting from the Max reading which is completely random, which totals 8 - 15 minute readings.

Any help on this is greatly appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of questions:

1. What should happen if the maximum value happens to occur more than once in column B?

2. What should happen if the maximum value occurs within the last 7 entries in the column, meaning you don't have 8 to average?

 

[Crazysgt]

#1
There will only be one Max in column B. This spreadsheet prints daily, at 23:45 hrs, only contains data for that day (24 hrs)-24:00-23:45 noted as time in column A. While throughout that day I understand the max could possibly change throughout, but at the end of the day there will only be 1 Max reading.

#2
This one is a bit more tricky! I have seen this scenario pop up from time to time. Normally we would just find the Max for that day and go in reverse. This happens very rarely. So I'm not too bothered on that part.

Hope you can shed some light?

Thanks

 

[offthelip]

try this:
=AVERAGE((OFFSET(B1,MATCH(MAX(B1:B96),B1:B96,0)-1,0,8)))

 

[markmzz]

Another way (without OFFSET):

=AVERAGE(INDEX($B$1:$B$96,MATCH(MAX($B$1:$B$96),$B$1:$B$96,0)):
INDEX($B$1:$B$96,MATCH(MAX($B$1:$B$96),$B$1:$B$96,0)-6))

Markmzz

 

[Peter_SSs]

It seems like this is a one-off formula so using the volatile function OFFSET shouldn't be a problem. However, if you did want to avoid it, I think Mark's formula needs a slight adjustment to
=AVERAGE(INDEX(B1:B96,MATCH(MAX(B1:B96),B1:B96,0)):INDEX(B1:B96,MATCH(MAX(B1:B96),B1:B96,0)+7))

Normally we would just find the Max for that day and go in reverse.

I'm not sure just what that means. For the following example, assuming 1199 is the max for the column, what cells should be averaged?

Book1
	B
85	1097
86	1077
87	1127
88	1192
89	1022
90	1055
91	1087
92	1009
93	1116
94	1199
95	1040
96	1029
97
Av after max

 

[markmzz]

I think Mark's formula needs a slight adjustment to
=AVERAGE(INDEX(B1:B96,MATCH(MAX(B1:B96),B1:B96,0)):INDEX(B1:B96,MATCH(MAX(B1:B96),B1:B96,0)+7))

Hi Peter,

Sorry, I didn't understand what the user wanted.

Thanks again for helps with my suggestion.

Here are two suggestions for the user:

​

=AVERAGE(INDEX(B1:B96,MIN(MATCH(MAX(B1:B96),B1:B96,0),89)):
INDEX(B1:B96,MIN(MATCH(MAX(B1:B96),B1:B96,0)+7,96)))

Or this small modification in Offthelip's formula:

=AVERAGE(OFFSET(B1,MIN(MATCH(MAX(B1:B96),B1:B96,0),89)-1,,8))

Markmzz

 

[offthelip]

Markmzz, i think that in the modification to my formula your red "min" should be a "max" otherwise it will always average the last 8 numbers. I an working on a tablet at the moment so i can't paste the formula

 

[markmzz]

Markmzz, i think that in the modification to my formula your red "min" should be a "max" otherwise it will always average the last 8 numbers. I an working on a tablet at the moment so i can't paste the formula

Hi Offthelip,

Maybe I'm wrong, but look at this:

[TABLE="class: grid, width: 539"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]00:00[/TD]
[TD="align: right"]5360[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Max[/TD]
[TD="align: right"]9999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00:15[/TD]
[TD="align: right"]5213[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]21:00[/TD]
[TD="align: right"]7782[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD]21:15[/TD]
[TD="align: right"]9778[/TD]
[TD][/TD]
[TD]Average[/TD]
[TD]Formula01:[/TD]
[TD="align: right"]5715,75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]21:30[/TD]
[TD="align: right"]3626[/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula02:[/TD]
[TD="align: right"]5715,75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]21:45[/TD]
[TD="align: right"]7779[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]22:00[/TD]
[TD="align: right"]4384[/TD]
[TD][/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]22:15[/TD]
[TD="align: right"]8592[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]91[/TD]
[TD]22:30[/TD]
[TD="align: right"]3911[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]92[/TD]
[TD]22:45[/TD]
[TD="align: right"]9999[/TD]
[TD][/TD]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]93[/TD]
[TD]23:00[/TD]
[TD="align: right"]9839[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]94[/TD]
[TD]23:15[/TD]
[TD="align: right"]1005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]23:30[/TD]
[TD="align: right"]1109[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]23:45[/TD]
[TD="align: right"]6887[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]******[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]***********[/TD]
[TD]*********[/TD]
[TD]**[/TD]
[TD]*****[/TD]
[TD]********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

The Max between 89 e 92 is 92 and the Min between 89 e 92 is 89. Think about. You have 7 values after 92?

My suggestion to the user, in this case, is to calc the average of the last 8 numbers.

Markmzz

 

[offthelip]

No , you are correct, it should be the minimum of the match and 89!!