Maximum 7 Day Average from data range

Calv1

New Member
Joined
Oct 4, 2013
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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:

Site01/01/202102/01/202103/01/20104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/2021
Site 1668851223311558899100
Site 255552512141820262830
Site 3444425013722115800500600

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

SiteMax 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
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 4, 2013
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 4, 2013
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
62,598
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 4, 2013
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,598
Office Version
  1. 365
Platform
  1. Windows
How about
+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)))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top