Rolling average

[ssh99]

I need a rolling average in cell AA4-AA6 for data from the last 6 months covering columns C to Z.

Each month data may be added to the next column which is why I have specified a range.

There are some months where there is no data available.

The spreadsheet can be accessed on the following link: Template.xlsx

Any help would be grately appreciated! I've been searching for a solution using the OFFSET formula for hours but it is not working,

 

[kevin9999]

If you're open to using a UDF, please try the following in a standard module. I think I understand your logic - average the most recent 6 cells that are not blank?

Function AVG6M() As Double
    Application.Volatile
    Dim c As Range, i As Long, j As Long, x As Long
    Set c = Application.Caller
    For i = 1 To 24
        If c.Offset(0, -i) <> "" Then
            x = x + 1
            j = j + c.Offset(0, -i).Value
            If x = 6 Then Exit For
        End If
    Next i
    AVG6M = j / x
End Function

Used like this:

Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	SUMMARY TABLE		RESULTS SUMMARY APRIL 2022 - MARCH 2023												RESULTS SUMMARY APRIL 2023 - MARCH 2024
2		Month	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	ROLLING AVERAGE	AVERAGE	AVERAGE
3	User	Name	2022	2022	2022	2022	2022	2022	2022	2022	2022	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2024	2024	2024	6 MONTHS	2022-2023	2023-2024
4	LSE-01	John Smith	0.00	5.00	4.00	10.00	10.00	1.00		5.00	3.00			5.00		1.00											4.17	4.78	1.00
5	LSE-02	Tracy Hills	3.00	4.00	5.00	5.00	4.00	0.00	4.00		3.00	3.00	3.00	5.00	3.00	1.00	5.00										3.33	3.55	3.00
6	LSE-03	Jill King	0.00	0.00	5.00	5.00	4.00	0.00		3.00	5.00			10.00	3.00		4.00										4.17	3.56	3.50
Sheet1

Cell Formulas
Range	Formula
AA4:AA6	AA4	=AVG6M()
AB4:AB6	AB4	=(IFERROR(AVERAGEIF(C4:N4,">=0"),"-"))
AC4:AC6	AC4	=(IFERROR(AVERAGEIF(O4:Z4,">=0"),"-"))

 

[ssh99]

If you're open to using a UDF, please try the following in a standard module. I think I understand your logic - average the most recent 6 cells that are not blank?

Function AVG6M() As Double
    Application.Volatile
    Dim c As Range, i As Long, j As Long, x As Long
    Set c = Application.Caller
    For i = 1 To 24
        If c.Offset(0, -i) <> "" Then
            x = x + 1
            j = j + c.Offset(0, -i).Value
            If x = 6 Then Exit For
        End If
    Next i
    AVG6M = j / x
End Function

Used like this:

Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	SUMMARY TABLE		RESULTS SUMMARY APRIL 2022 - MARCH 2023												RESULTS SUMMARY APRIL 2023 - MARCH 2024
2		Month	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	ROLLING AVERAGE	AVERAGE	AVERAGE
3	User	Name	2022	2022	2022	2022	2022	2022	2022	2022	2022	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2023	2024	2024	2024	6 MONTHS	2022-2023	2023-2024
4	LSE-01	John Smith	0.00	5.00	4.00	10.00	10.00	1.00		5.00	3.00			5.00		1.00											4.17	4.78	1.00
5	LSE-02	Tracy Hills	3.00	4.00	5.00	5.00	4.00	0.00	4.00		3.00	3.00	3.00	5.00	3.00	1.00	5.00										3.33	3.55	3.00
6	LSE-03	Jill King	0.00	0.00	5.00	5.00	4.00	0.00		3.00	5.00			10.00	3.00		4.00										4.17	3.56	3.50
Sheet1

Cell Formulas
Range	Formula
AA4:AA6	AA4	=AVG6M()
AB4:AB6	AB4	=(IFERROR(AVERAGEIF(C4:N4,">=0"),"-"))
AC4:AC6	AC4	=(IFERROR(AVERAGEIF(O4:Z4,">=0"),"-"))

Thank you for your reply. I don't recognise the VBA you've added unfortunately.

I only need the rolling average formula in column AA. The formula you've added seems quite simplified?

The formulas already in columns AB and AC are OK.

 

[kevin9999]

Yes, I understand what you're looking for. To see how the VBA works (UDF = User Defined Function) press Alt+F11 - the Visual Basic Editor will appear on your screen. From the menu at the top of the screen, select Insert/Module. Copy the code from post #2 above and paste it into the window that appears on the right of the screen.

To use the UDF, simply type =AVG6M() into cell AA4. The result of the average of the last 6 cells with a value in them in row 4 should appear. Manually check the figure to see if it's what you want.

I didn't do anything with the formulas in columns AB & AC, I left them as you had provided them in your shared file.

 

[ssh99]

Yes, I understand what you're looking for. To see how the VBA works (UDF = User Defined Function) press Alt+F11 - the Visual Basic Editor will appear on your screen. From the menu at the top of the screen, select Insert/Module. Copy the code from post #2 above and paste it into the window that appears on the right of the screen.

To use the UDF, simply type =AVG6M() into cell AA4. The result of the average of the last 6 cells with a value in them in row 4 should appear. Manually check the figure to see if it's what you want.

I didn't do anything with the formulas in columns AB & AC, I left them as you had provided them in your shared file.

Thanks. I see what you've done and is calculating correctly. Is there a formula I can put directly in the cell instead? I want to avoid using VBA as then I may need to take the file macro enabled when sharing with other people.

 

[kevin9999]

There may be. Formulas are not my thing, so hopefully another contributor will come to the rescue.

 

[ssh99]

Yes, I understand what you're looking for. To see how the VBA works (UDF = User Defined Function) press Alt+F11 - the Visual Basic Editor will appear on your screen. From the menu at the top of the screen, select Insert/Module. Copy the code from post #2 above and paste it into the window that appears on the right of the screen.

To use the UDF, simply type =AVG6M() into cell AA4. The result of the average of the last 6 cells with a value in them in row 4 should appear. Manually check the figure to see if it's what you want.

I didn't do anything with the formulas in columns AB & AC, I left them as you had provided them in your shared file.

Hi. I'm getting 'runtime error 424 when running the VBA, Screenshot of the line where it fails below.

Any idea what the problem is?

 

[kevin9999]

Could you possibly share your file via DropBox, Google Drive or similar file sharing platform - IF the file your applying this to is different from the one you shard in post #1.

 

[ssh99]

Could you possibly share your file via DropBox, Google Drive or similar file sharing platform - IF the file your applying this to is different from the one you shard in post #1.

It's the same file I shared in post #1. Is it not opening anymore?

 

[kevin9999]

It's the same file I shared in post #1. Is it not opening anymore?

Yes, it's opening, but I assume you saved it in macro enabled or binary format after you added the code?