VBA get AVE MARGIN of VICTORY (or DEFEAT)

[Roscoe_Word]

Hello SBR Crew. Hope everyone is holding up well during the pause.

I have a teams' margs of victory in cells in column C

I just want VBA to show the AVERAGE of the last 5 cells in that column, column C

Does anyone know this line of code?
-------------------------------------------------------
L5AveMarg = Average of last 5 cells in column C
---------------------------------------------------------
I know I have this display right

MsgBox "Last 5 Ave = " & L5AveMarg
-------------------------------------------------------------

I know there are (Worksheet Functions) and (application functions) but I just can't get it right.

Thanks fellas.

I'm hoping only another couple of weeks before the sports betting world returns to normal.

[Waterstpub87]

Code in next post

[Waterstpub87]

Oh ok, you always need it to dynamically select the last five cells of column C:

Add this to your code:

Sub Getlast5average()
Bottom = Range("C1").End(xlDown)
myrng = Range(Cells(Bottom - 4, "C"), Cells(Bottom, "C"))

ls5m = Application.WorksheetFunction.Average(my rng)

MsgBox (ls5m)
End Sub

[Roscoe_Word]

Thanks WP. I think I'm gettin close, however I get this when running the program:

Run-time error '1004':
Unable to get the Average property of the WorksheetFunction class

The last 5 ave in column C should be 12.4

date	line	marg
April 2, 2020	7.5	11.0
April 3, 2020	7.5	11.0
April 4, 2020	8.0	4.0
April 5, 2020	8.0	10.0
April 6, 2020	7.5	9.0
April 7, 2020	7.5	6.0
April 8, 2020	9.0	5.0
April 9, 2020	9.0	7.0
April 10, 2020	8.0	3.0
April 11, 2020	7.5	7.0
April 12, 2020	7.5	9.0
April 13, 2020	15.0	36.0

I took out the space in (my rng) in line 4 of the code, making it (myrng) and
I took out the parenthesis in line 5 of the code: MsgBox (ls5m) to MsgBox ls5m

Thanks again for the help, WP

[Waterstpub87]

If you just use a formula, can you get an average? It might be because your items in C might be strings, and not floats.

Correct on the parenthesis in line five. I am too used to python now, where the command is print()

I pasted the code into another workbook and ran it, with 1 2 3 4 5 in column C and it worked fine.

[Roscoe_Word]

Excellet....Thank you.

I tweaked this line and its workin good now.

Bottom = Range("C1").End(xlDown)

TO:

Bottom = Range("C1").End(xlDown).Row

Thanks again my friend. Lets get this Corona thing over and get life back to normal.

[Waterstpub87]

Excellet....Thank you.

I tweaked this line and its workin good now.

Bottom = Range("C1").End(xlDown)

TO:

Bottom = Range("C1").End(xlDown).Row

Thanks again my friend. Lets get this Corona thing over and get life back to normal.