# Maximum 7 Day Average from data range

#### Calv1

##### New Member
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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### offthelip

##### Well-known Member
try this code:
VBA 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

##### Board Regular
Formula option:
Test.xlsx
ABCDEFGHIJKL
1Site1/1/20212/1/202103/01/2014/1/20215/1/20216/1/20217/1/20218/1/20219/1/202110/1/2021Max 7 Day Average
2Site 166885122331155889910078.14
3Site 25555251214182026283034.14
4Site 3444425013722115800500600339.29
Sheet1
Cell Formulas
RangeFormula
L2:L4L2=AVERAGE(LARGE(\$B2:\$K2,ROW(\$1:\$7)))

[Edit to place at the row end]

Last edited:

#### Calv1

##### New Member
Formula option:
Test.xlsx
ABCDEFGHIJKL
1Site1/1/20212/1/202103/01/2014/1/20215/1/20216/1/20217/1/20218/1/20219/1/202110/1/2021Max 7 Day Average
2Site 166885122331155889910078.14
3Site 25555251214182026283034.14
4Site 3444425013722115800500600339.29
Sheet1
Cell Formulas
RangeFormula
L2:L4L2=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

##### Board Regular

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

##### New Member
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

##### Well-known Member

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

##### MrExcel MVP, Moderator
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

##### New Member
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

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEFGHIJKLM
1Site01/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/2021
2Site 166885122331155889910058.28571
3Site 25555251214182026283028.42857
4Site 3444425013722115800500600296.4286
Main
Cell Formulas
RangeFormula
M2:M4M2=MAX(SUBTOTAL(1,OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B:K)-6))-1,,7)))

Replies
2
Views
101
Replies
4
Views
81
Replies
5
Views
126
Replies
6
Views
117
Replies
3
Views
84

1,141,769
Messages
5,708,420
Members
421,568
Latest member
Huxley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back