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

Reboshua

Board Regular
Joined
Jan 23, 2015
Messages
83
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
Date2PLAYER FULL NAMEFD5DMA3DMA
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:

Some videos you may like

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
Joined
Mar 30, 2018
Messages
78
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
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 23, 2015
Messages
83
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 23, 2015
Messages
83
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
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date2</td><td style=";">PLAYER FULL NAME</td><td style=";">FD</td><td style=";">5DMA</td><td style=";">3DMA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">10/17/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">44.2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">10/19/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">29</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">10/20/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">46.9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">36.6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">10/22/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">19.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">37.95</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">10/25/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">25.8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">10/27/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">20</td><td style="text-align: right;;">33</td><td style="text-align: right;;">22.45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">10/30/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">44.5</td><td style="text-align: right;;">28.16</td><td style="text-align: right;;">22.9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">11/02/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">9.6</td><td style="text-align: right;;">31.26</td><td style="text-align: right;;">32.25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">11/04/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">37.6</td><td style="text-align: right;;">23.8</td><td style="text-align: right;;">27.05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">11/05/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">37.3</td><td style="text-align: right;;">27.5</td><td style="text-align: right;;">23.6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">11/07/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">44</td><td style="text-align: right;;">29.8</td><td style="text-align: right;;">37.45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">11/09/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">34.4</td><td style="text-align: right;;">34.6</td><td style="text-align: right;;">40.65</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">11/12/2018</td><td style=";">Aaron Gordon</td><td style="text-align: right;;">44.5</td><td style="text-align: right;;">32.58</td><td style="text-align: right;;">39.2</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">All</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=Reboshua(<font color="Blue">$B$2:$B$14,5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=Reboshua(<font color="Blue">$B$2:$B$14,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Reboshua

Board Regular
Joined
Jan 23, 2015
Messages
83
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
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
Does your data look the same as my test data in post#7?
 

Reboshua

Board Regular
Joined
Jan 23, 2015
Messages
83
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top