Function to get moving average of last 5 rows that contain "x"

[Reboshua]

I have a spreadsheet that has data for player performances in NBA fantasy sports. A sample of that is shown below, but the actual file has thousands of rows and contains data for all of the players, not just Aaron Gordon below.

I'd like to learn how to create a custom function to be used in columns D and E that will calculate an "x" day moving average for the data in column C where X is whatever I define it to be in the function. In this case, it would be 5 days for column D and 2 for column E, but I'd like the ability to easily manipulate that.

I was watching a video where the gentleman had the following code, but when I ran it on my sheet, I got an "end if without a block if" error and I didn't understand how to debug it. I don't know VB. Here is his code.

Thanks in advance!

~~~~~~~~~~~~~~~~

Public Function MAVG(PlayerName, Games, RowStart) As Single

'Use this only on Worksheet PlayerbyGame

Dim Game_Cnt As Integer
Dim N As Long

Dim FD As Single
Dim Sigma_Score
'Dim Game_Cnt as Interger
Dim Temp_Name As String

For N = RowStart To 4 Step -1
Temp_Name = Worksheets("PlayerbyGame").Cells(N, 1)

If InStr(Temp_Name, PlayerName) > 0 Then
Sigma_Score = Sigma_Score + Worksheets("PlayerbyGame").Cells(N, 31)
Game_Cnt = Game_Cnt + 1


End Function
~~~~~~~~~~~~~~~~~~~~~~~~

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Date2[/td][td]PLAYER FULL NAME[/td][td]FD[/td][td]5DMA[/td][td]3DMA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/17/2018​

[/td][td]Aaron Gordon[/td][td]

44.2​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/19/2018​

[/td][td]Aaron Gordon[/td][td]

29​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/20/2018​

[/td][td]Aaron Gordon[/td][td]

46.9​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/22/2018​

[/td][td]Aaron Gordon[/td][td]

19.1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/25/2018​

[/td][td]Aaron Gordon[/td][td]

25.8​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/27/2018​

[/td][td]Aaron Gordon[/td][td]

20​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/30/2018​

[/td][td]Aaron Gordon[/td][td]

44.5​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11/2/2018​

[/td][td]Aaron Gordon[/td][td]

9.6​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11/4/2018​

[/td][td]Aaron Gordon[/td][td]

37.6​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11/5/2018​

[/td][td]Aaron Gordon[/td][td]

37.3​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11/7/2018​

[/td][td]Aaron Gordon[/td][td]

44​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11/9/2018​

[/td][td]Aaron Gordon[/td][td]

34.4​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11/12/2018​

[/td][td]Aaron Gordon[/td][td]

44.5​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: PlayerbyGame[/td][/tr][/table]

 

[Fluff]

It always helps if you give us exact details, rather than trying to simplify things.

Try

Function Reboshua(Rng1 As Range, Rng2 As Range, Dys As Long) As Double
   Dim i As Long, Rw As Long, Cnt As Long
   Dim Plyr As String

   Rw = Application.Caller.Cells.Row - 1
   Plyr = Rng1.Cells(Rw, 1)
   For i = Rw - 1 To 1 Step -1
      If Rng1(i, 1) = Plyr Then
         Cnt = Cnt + 1
         Reboshua = Reboshua + Rng2(i, 1).Value
      End If
      If Cnt = Dys Then
         Reboshua = Reboshua / Cnt
         Exit Function
      End If
   Next i
   If Cnt < Dys Then Reboshua = 0
End Function

Used like
=Reboshua($B$2:$B$14,$C$2:$C$14,5)

 

[Reboshua]

Fluff - thanks again for your help. I think this worked, but several times excel hung for a long time and when I hit escape I got a debug prompt. My sheet has 22000 rows or so - so I don't know if there's an actual bug or if it's just taking a long time to cycle through and is being interrupted when I hit Escape.

Thoughts?

 

[Fluff]

With 22,000 rows it will take quite some time.

 

[Reboshua]

I was patient with it this morning and it worked brilliantly. Thank you again for your help!

 

[Fluff]

You're welcome & thanks for the feedback