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

Reboshua

Board Regular
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.

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

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
 Date2 PLAYER FULL NAME FD 5DMA 3DMA 10/17/2018​ Aaron Gordon 44.2​ 10/19/2018​ Aaron Gordon 29​ 10/20/2018​ Aaron Gordon 46.9​ 10/22/2018​ Aaron Gordon 19.1​ 10/25/2018​ Aaron Gordon 25.8​ 10/27/2018​ Aaron Gordon 20​ 10/30/2018​ Aaron Gordon 44.5​ 11/2/2018​ Aaron Gordon 9.6​ 11/4/2018​ Aaron Gordon 37.6​ 11/5/2018​ Aaron Gordon 37.3​ 11/7/2018​ Aaron Gordon 44​ 11/9/2018​ Aaron Gordon 34.4​ 11/12/2018​ Aaron Gordon 44.5​
 Sheet: PlayerbyGame

Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Swayzy

Board Regular
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

MrExcel MVP, Moderator
You are missing the End If from here
Code:
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

Board Regular
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."

Thank you.

Fluff

MrExcel MVP, Moderator

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

Board Regular
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

Last edited:

Fluff

MrExcel MVP, Moderator

Code:
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

Reboshua

Board Regular
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

MrExcel MVP, Moderator
Does your data look the same as my test data in post#7?

Reboshua

Board Regular
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.

Replies
1
Views
86
Replies
3
Views
61
Replies
12
Views
477
Replies
3
Views
361
Replies
22
Views
600

1,109,425
Messages
5,528,687
Members
409,830
Latest member
KT50

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...