Average of last 3 columns

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!

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)

"Joanne R." <[email protected]> wrote in message
news:[email protected]...
> 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!

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)

"Joanne R." <[email protected]> wrote in message
news:[email protected]...
> 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!

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


Gary


"Joanne R." <[email protected]> wrote in message
news:[email protected]...
>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!