Average of last 3 columns

[Guest]

I have a spreadsheet that has the months in columns and names in rows. It
holds historical YTD information for the people, one number for each month.
It also has a column that will average the months. A is the name, B is the
average C-N equals Jan-Dec.

I am building a macro that will open this file, find the name and get the
YTD average. Easy enough. Now I need it to have it find the name and
calculate the average of just the last three months of the data available in
the sheet. This of course will vary depending on when I use this file.
Right now, data is through March, so I need Jan-Mar averaged. Next month, I
will need to calculate Feb-Apr.

Any thoughts? Thanks in advance for your help!

 

[Bob Phillips]

You can use this to get that average

=AVERAGE(INDEX(C11:N11,MONTH(TODAY())-2):INDEX(C11:N11,MONTH(TODAY())))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

This version will cater for Jan and Feb as well

=AVERAGE(INDEX(C11:N11,MAX(1,MONTH(TODAY())-2)):INDEX(C11:N11,MONTH(TODAY())
))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Gary Keramidas]

maybe some code like this. i assumed the names were in a2 and down and the
months were in b1 across


Sub test()
Dim i As Long
Dim lastrow As Long
Dim lastcol As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
lastcol = Worksheets("Sheet1").Cells(2, 1).End(xlToRight).Column
For i = 2 To lastrow
Debug.Print lastcol
Range("n" & i).Value = Application.WorksheetFunction.Average(Range(Cells(i, _
lastcol - 2), Cells(i, lastcol)))
Next

End Sub