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]

 

[Swayzy]

The row that begins with If instr( and ends with Then is the problem. Try putting all the three rows beneath on the same row(Line) as the if statement.

 

[Fluff]

You are missing the End If from here

If InStr(Temp_Name, PlayerName) > 0 Then
    Sigma_Score = Sigma_Score + Worksheets("PlayerbyGame").Cells(N, 31)
    Game_Cnt = Game_Cnt + 1
[COLOR=#0000ff] End If[/COLOR]

End Function

 

[Reboshua]

Thanks for the replies so far. I correctly indented the code in the workbook. I also added the End If statement.

I am now getting a "for without next" error and don't know what to do about that.

The more important problem for me is indicating the row number to start from. I need each row to calculate the 5 previous values in the sheet for Aaron Gordon, but I don't know how in the function to indicate "current row."

Any advice?

Thank you.

 

[Fluff]

As the code you posted is incomplete & doesn't do anything, can you please explain exactly what you want & show examples of the expected result

 

[Reboshua]

Fluff, thank you for the fast reply. I tried to articulate what I wanted in my original post above. I was hoping his code would be an easy bridge, but perhaps not.

I'd like a formula/function in D2 (and similarly E2) that will calculate the moving average of values in column C for the number of days I indicate in the function, but will only consider those rows above the current row and only calculate the values for the players who match the text string in column B.

To be specific, I want the value in D7 to equal 33 and the value in D8 to equal 28.16.

TIA,

David

 

[Fluff]

Ok, how about

Function Reboshua(Rng 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 = Rng.Cells(Rw, 1)
   For i = Rw - 1 To 1 Step -1
      If Rng(i, 1) = Plyr Then
         Cnt = Cnt + 1
         Reboshua = Reboshua + Rng(i, 2).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

Excel 2013/2016
	A	B	C	D	E
1	Date2	PLAYER FULL NAME	FD	5DMA	3DMA
2	10/17/2018	Aaron Gordon	44.2	0	0
3	10/19/2018	Aaron Gordon	29	0	0
4	10/20/2018	Aaron Gordon	46.9	0	36.6
5	10/22/2018	Aaron Gordon	19.1	0	37.95
6	10/25/2018	Aaron Gordon	25.8	0	33
7	10/27/2018	Aaron Gordon	20	33	22.45
8	10/30/2018	Aaron Gordon	44.5	28.16	22.9
9	11/02/2018	Aaron Gordon	9.6	31.26	32.25
10	11/04/2018	Aaron Gordon	37.6	23.8	27.05
11	11/05/2018	Aaron Gordon	37.3	27.5	23.6
12	11/07/2018	Aaron Gordon	44	29.8	37.45
13	11/09/2018	Aaron Gordon	34.4	34.6	40.65
14	11/12/2018	Aaron Gordon	44.5	32.58	39.2
All

Cell Formulas
Range	Formula
D2	=Reboshua($B$2:$B$14,5)
E2	=Reboshua($B$2:$B$14,2)

 

[Reboshua]

Thanks for trying, but that didn't work for me. I don't see in the code any reference to the values in column C, which is what I need to average. When I run this code, I get a 0 in d2 and #Value ! errors in each successive cell in the column below D2.

Thoughts?

 

[Fluff]

Does your data look the same as my test data in post#7?

 

[Reboshua]

Not exactly, no. There are other columns and there likely will be some additional ones added over time, so I need the function to allow me to indicate where the data is, if that makes sense.

Really appreciate you helping me here.