Maximum 7 Day Average from data range

[Calv1]

Hi,

I have a problem that I can't find a solution for. I have a table that retrieves total production from a site on a given day. This ranges back 3 years.

What I want to be able to find is the maximum 7 day average throughout that period. Any suggestions on the best way to achieve this would be most appreciated. All moving averages I have found do not seem to work dynamically in the way required.

Example table:

Site	01/01/2021	02/01/2021	03/01/201	04/01/2021	05/01/2021	06/01/2021	07/01/2021	08/01/2021	09/01/2021	10/01/2021
Site 1	66	88	51	22	33	11	55	88	99	100
Site 2	55	55	25	12	14	18	20	26	28	30
Site 3	44	44	250	137	22	11	5	800	500	600

From this, I then have a separate table as such;

Site	Max 7 Day Average
Site 1
Site 2
Site 3

What I'd like to do is have a formula, or a macro, that works through the data row by row, calculates the 7 day averages and at the end of it gives me the maximum 7 day average.

Any help or suggestions would be most appreciated.

 

[offthelip]

try this code:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
inarr = Range(Cells(1, 1), Cells(lastrow, lastcol))
' pick up column and b for the output colummn b gets over written
outarr = Range(Cells(1, 1), Cells(lastrow, 2))
cnt = 1
sm = 0
mxsm = 0
For i = 2 To lastrow
    For j = 2 To lastcol
      If cnt < 7 Then
       sm = sm + inarr(i, j)
       cnt = cnt + 1
      Else
       sm = sm + inarr(i, j)
       If sm > mxsm Then
         mxsm = sm
       End If
       sm = 0
       cnt = 1
      End If
    Next j
outarr(i, 2) = mxsm
mxsm = 0
cnt = 0
sm = 0
Next i
With Worksheets("Sheet2")
outarr(1, 2) = "Max 7 Day Average"
.Range(.Cells(1, 1), .Cells(lastrow, 2)) = outarr
End With
End Sub

 

[Habtest]

Formula option:

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Site	1/1/2021	2/1/2021	03/01/201	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021	9/1/2021	10/1/2021	Max 7 Day Average
2	Site 1	66	88	51	22	33	11	55	88	99	100	78.14
3	Site 2	55	55	25	12	14	18	20	26	28	30	34.14
4	Site 3	44	44	250	137	22	11	5	800	500	600	339.29
Sheet1

Cell Formulas
Range	Formula
L2:L4	L2	=AVERAGE(LARGE($B2:$K2,ROW($1:$7)))

[Edit to place at the row end]

 

[Calv1]

Formula option:

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Site	1/1/2021	2/1/2021	03/01/201	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021	9/1/2021	10/1/2021	Max 7 Day Average
2	Site 1	66	88	51	22	33	11	55	88	99	100	78.14
3	Site 2	55	55	25	12	14	18	20	26	28	30	34.14
4	Site 3	44	44	250	137	22	11	5	800	500	600	339.29
Sheet1

Cell Formulas
Range	Formula
L2:L4	L2	=AVERAGE(LARGE($B2:$K2,ROW($1:$7)))

[Edit to place at the row end]

Thanks for this. I'm just trying to understand the formula and how the row function comes in to it?

I've tried the formula with just "1" in the end as opposed to the row and receiving the same result.

I'm not 100% convinced that this is getting the 7 day average. If I look at the data in the sample table, and use site 3 as an example. The maximum 7 day average would be for the 7 days E4:K4

Total 2075 / 7 = 296.429

 

[Habtest]

Hi, the ROW() part in the end activates a loop to find top 7 figures in the row. Replacing it with "1" changes the result.

For site 3, I think the max 7 days is B-E, I-K, which sum up to 2,375 and average at 339.29, same as the formula result.

If there's still issue, can you upload what you see by Xlbb2?

 

[Calv1]

Hi, the ROW() part in the end activates a loop to find top 7 figures in the row. Replacing it with "1" changes the result.

For site 3, I think the max 7 days is B-E, I-K, which sum up to 2,375 and average at 339.29, same as the formula result.

If there's still issue, can you upload what you see by Xlbb2?

Thanks for the explanation. I see what you mean now, and this is perhaps my fault for not being clearer in the question.

I'm looking to retrieve the highest consecutive 7 day period.

One word that makes the world of difference, apologies for that omission.

 

[offthelip]

Have you tried the code I wrote? this code averages 1/1/20201 to 7/1/2021 and then 8/1/2021 to 15/1/2021. is this what you wanted?? It could be modified to average
1/1/2021 to 7/1/20201 and then 2/1/2021 to 8/1/2021. I am still not sure which you wanted

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Calv1]

Have you tried the code I wrote? this code averages 1/1/20201 to 7/1/2021 and then 8/1/2021 to 15/1/2021. is this what you wanted?? It could be modified to average
1/1/2021 to 7/1/20201 and then 2/1/2021 to 8/1/2021. I am still not sure which you wanted

Hi, I have and noticed that this worked this way. I will try and amend...

It's close. What I'd like to do is average 1/1 - 7/1, then 2/1 - 8/1 , 3/1 - 9/1 , 4/1 - 10-1 etc. and take away from that the highest value to give the maximum consecutive 7 day average for each site.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Site	01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021	06/01/2021	07/01/2021	08/01/2021	09/01/2021	10/01/2021
2	Site 1	66	88	51	22	33	11	55	88	99	100		58.28571
3	Site 2	55	55	25	12	14	18	20	26	28	30		28.42857
4	Site 3	44	44	250	137	22	11	5	800	500	600		296.4286
Main

Cell Formulas
Range	Formula
M2:M4	M2	=MAX(SUBTOTAL(1,OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B:K)-6))-1,,7)))