How to get AVERAGE of 3 previous sheets automatically when you create a new sheet

[shahdelsol]

I am trying to get AVERAGE of 3 previous sheets of the same cells to the left of the last sheet on a workbook every time I am creating a new sheet. Can you do this by formula? I feel like you can't do this unless you manually update the formula each time you create a new sheet. Does anyone know if there is a way to do this by formula as it gets updated automatically when a new sheet created? If not how can this be done through VBA. Let's say I am looking for the average of L58 of the 3 previous sheets to the left of the last sheet.

I appreciate anyone who can help. ​

 

[shahdelsol]

I have tried the following code but it didn't work. To add for more clarification, the sheets have been named with the month and the year like (Jul 2018, Aug 2018....) and what I am trying to do is to get average of 3 cells of the 3 previous sheets or 3 moths in another word. Every month I copy and create a new sheet from template and want to be able to put a formula in the cell that gives AVERAGE based on 3 previous sheets (months).

[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     [/FONT][/COLOR][COLOR=#222222][FONT=Verdana]Dim sh1 As Worksheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     Dim sh2 As Worksheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     Dim sh3 As Worksheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     Set sh1 = Worksheets(Worksheets.Count - 1)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     Set sh2 = Worksheets(Worksheets.Count - 2)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     Set sh3 = Worksheets(Worksheets.Count - 3)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     Range("L59").Value = "= AVERAGE(sh1.range (""L58""), sh2.range(""L58""), sh3.range(""L58""))"[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

​

I am trying to get AVERAGE of 3 previous sheets of the same cells to the left of the last sheet on a workbook every time I am creating a new sheet. Can you do this by formula? I feel like you can't do this unless you manually update the formula each time you create a new sheet. Does anyone know if there is a way to do this by formula as it gets updated automatically when a new sheet created? If not how can this be done through VBA. Let's say I am looking for the average of L58 of the 3 previous sheets to the left of the last sheet.

I appreciate anyone who can help. ​

 

[Eric W]

Given that your sheet names are sequential dates, there are a couple ways you can do this.

	A	B	C	D
Oct 2018

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12.33333[/TD]
[TD="align: right"]12.33333[/TD]
[TD="align: right"][/TD]

</tbody>

Oct 2018​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]=TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT(" ",200)),50))[/TD]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=(INDIRECT("'"&TEXT(EOMONTH(DATEVALUE(1&A2),-1),"mmm yyyy")&"'!L58")+
INDIRECT("'"&TEXT(EOMONTH(DATEVALUE(1&A2),-2),"mmm yyyy")&"'!L58")+
INDIRECT("'"&TEXT(EOMONTH(DATEVALUE(1&A2),-3),"mmm yyyy")&"'!L58"))/3[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=Avg3Prev(L58)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If you use straight formulas, enter the A2 formula. This gives the name of the current sheet. Then by using date functions to subtract 1, 2, and 3 from the month, we can use INDIRECT to get the value from the last 3 sheet names.

If you want to use VBA, you can use a function, like the C2 formula. Here's the code:

Function Avg3Prev(ByRef target As Range)
Dim sn As String, s2 As String, i As Long

    sn = target.Parent.Name
    For i = -1 To -3 Step -1
        s2 = Evaluate("TEXT(EOMONTH(DATEVALUE(""1" & sn & """)," & i & "),""mmm yyyy"")")
        Avg3Prev = Avg3Prev + Sheets(s2).Range(target.Address)
    Next i
    Avg3Prev = Avg3Prev / 3
        
End Function

It shouldn't be too hard to adapt to a regular procedure if you want. Hope this helps!

 

[Peter_SSs]

I'm not sure why you are using both L59 & L58? I thought you wanted the formula to calculate the same cell the formula is in, but on the 3 previous sheets. If that is the case, you could try this (vba) user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below*.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

* The default is to use the previous 3 sheets but if you wanted to average a different number of sheets, use the optional argument like in cell L59 below

Note that function does not work back from the last worksheet in the workbook, but from the sheet the formula is in, which may or may not be the same thing.
Also the function will only update when any cell on that worksheet re-calculates.

Function AvPrev(Optional N As Long = 3) As Variant
  Dim Vals As Variant
  Dim i As Long, CurrIdx As Long
  Dim sAdr As String
  
  Application.Volatile
  CurrIdx = Application.Caller.Parent.Index
  sAdr = Application.Caller.Address
  ReDim Vals(1 To N) As Variant
  For i = 1 To N
    Vals(i) = Sheets(CurrIdx - i).Range(sAdr).Value
  Next i
  AvPrev = Application.Average(Vals)
End Function

Excel Workbook
	L
58	13.6667
59	37.5
Sheet13

 

[shahdelsol]

This is great. I like the formula much better and it does exactly what I am looking for. I keep VBA code for future reference. Thank you so much!

If you use straight formulas, enter the A2 formula. This gives the name of the current sheet. Then by using date functions to subtract 1, 2, and 3 from the month, we can use INDIRECT to get the value from the last 3 sheet names.

 

[shahdelsol]

I appreciate your response. I keep your vba code for future reference. Indirect formula by Eric did what I was exactly looking for. Thanks

you could try this (vba) user-defined function.

 

[Peter_SSs]

I appreciate your response. I keep your vba code for future reference.

You're welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only. So I have cut down the quotes in your posts.

 

[Eric W]

Glad we could help. I was playing around and found a shorter version of the B2 formula:

=AVERAGE(SUBTOTAL(1,INDIRECT("'"&TEXT(EOMONTH(DATEVALUE(1&A2),{-1,-2,-3}),"mmm yyyy")&"'!L58")))

 

[shahdelsol]

.

 

[shahdelsol]

You're welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only. So I have cut down the quotes in your posts.

Your note taken. Thanks again.