Last YTD average in columns with full year of data.

[Joe]

I have a previous year I need to average the months on. There are 12 months
of data. I need to compare the average to the number of months so far for
this year.
For example if in 2007 I had Jan through Dec as 1, 2, 1, 2, 1, 2, 1, 2, 1, 2
etc. and if we were currently in Nov then I want Excel to read the top of the
column that says "NOVEMBER" and only average up to that column. I thought of
using MONTH(TODAY())-1 to specify the 11 month and match to the heading of
NOVEMBER by reconginizing the text (NOVEMBER) with the formula MONTH(1&L2)
[L2 is the cell that has the text NOVEMBER in it. I just can't figure out how
to put it all together. I have to manually change the column reference by
hand each month so the average calculation only looks at the number of months
up to where we are currently.

 

[Bob Phillips]

Depends upon the columns, but let's assume the data is in columns B:M, you
could use

=SUM(B2:INDEX(B2:M2,1,MONTH(TODAY()))

 

[Max]

Assume B2:M2 houses the 12 month text labels in "mmmm" format: January, etc
Placed in say, N3:
=AVERAGE(OFFSET(A3,,MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0),,-MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0)))
would return the YTD average for row3. Copy N3 down as required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[Joe]

Thanks sooooo much!

With a little mod it works great.

Starting the sheet with January in the upper left corner of the sheet in A1
the following is what worked for me:
=AVERAGE(A2:INDEX(A1:L2,1,MONTH(TODAY())))

Thanks again

Depends upon the columns, but let's assume the data is in columns B:M, you
could use

=SUM(B2:INDEX(B2:M2,1,MONTH(TODAY()))

--
__________________________________
HTH

Bob

I have a previous year I need to average the months on. There are 12 months
of data. I need to compare the average to the number of months so far for
this year.
For example if in 2007 I had Jan through Dec as 1, 2, 1, 2, 1, 2, 1, 2, 1,
2
etc. and if we were currently in Nov then I want Excel to read the top of
the
column that says "NOVEMBER" and only average up to that column. I thought
of
using MONTH(TODAY())-1 to specify the 11 month and match to the heading of
NOVEMBER by reconginizing the text (NOVEMBER) with the formula MONTH(1&L2)
[L2 is the cell that has the text NOVEMBER in it. I just can't figure out
how
to put it all together. I have to manually change the column reference by
hand each month so the average calculation only looks at the number of
months
up to where we are currently.

 

[Joe]

That works great also but how would I modify it so it did not include the
current month in the calculation. In other words since I am closing November
in December I don't want to use December data and only want to average 11
months.

With =AVERAGE(GN19:INDEX(GN2:GY19,18,MONTH(TODAY()))) I just modified it to
=AVERAGE(GN19:INDEX(GN2:GY19,18,MONTH(TODAY())-1))

How would I do something similar with your solution?

Thank you

Assume B2:M2 houses the 12 month text labels in "mmmm" format: January, etc
Placed in say, N3:
=AVERAGE(OFFSET(A3,,MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0),,-MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0)))
would return the YTD average for row3. Copy N3 down as required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---

I have a previous year I need to average the months on. There are 12 months
of data. I need to compare the average to the number of months so far for
this year.
For example if in 2007 I had Jan through Dec as 1, 2, 1, 2, 1, 2, 1, 2, 1, 2
etc. and if we were currently in Nov then I want Excel to read the top of the
column that says "NOVEMBER" and only average up to that column. I thought of
using MONTH(TODAY())-1 to specify the 11 month and match to the heading of
NOVEMBER by reconginizing the text (NOVEMBER) with the formula MONTH(1&L2)
[L2 is the cell that has the text NOVEMBER in it. I just can't figure out how
to put it all together. I have to manually change the column reference by
hand each month so the average calculation only looks at the number of months
up to where we are currently.

 

[Bob Phillips]

Why would you need to if the other solution works?

--
__________________________________
HTH

Bob

That works great also but how would I modify it so it did not include the
current month in the calculation. In other words since I am closing
November
in December I don't want to use December data and only want to average 11
months.

With =AVERAGE(GN19:INDEX(GN2:GY19,18,MONTH(TODAY()))) I just modified it
to
=AVERAGE(GN19:INDEX(GN2:GY19,18,MONTH(TODAY())-1))

How would I do something similar with your solution?

Thank you

Assume B2:M2 houses the 12 month text labels in "mmmm" format: January,
etc
Placed in say, N3:
=AVERAGE(OFFSET(A3,,MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0),,-MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0)))
would return the YTD average for row3. Copy N3 down as required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---

I have a previous year I need to average the months on. There are 12
months
of data. I need to compare the average to the number of months so far
for
this year.
For example if in 2007 I had Jan through Dec as 1, 2, 1, 2, 1, 2, 1, 2,
1, 2
etc. and if we were currently in Nov then I want Excel to read the top
of the
column that says "NOVEMBER" and only average up to that column. I
thought of
using MONTH(TODAY())-1 to specify the 11 month and match to the heading
of
NOVEMBER by reconginizing the text (NOVEMBER) with the formula
MONTH(1&L2)
[L2 is the cell that has the text NOVEMBER in it. I just can't figure
out how
to put it all together. I have to manually change the column reference
by
hand each month so the average calculation only looks at the number of
months
up to where we are currently.

 

[Joe]

It works fine for month like November because I use the current month minus
1. But this won't work when I am in January since minus 1 will give me zero
which is not a month.

Why would you need to if the other solution works?

--
__________________________________
HTH

Bob

That works great also but how would I modify it so it did not include the
current month in the calculation. In other words since I am closing
November
in December I don't want to use December data and only want to average 11
months.

With =AVERAGE(GN19:INDEX(GN2:GY19,18,MONTH(TODAY()))) I just modified it
to
=AVERAGE(GN19:INDEX(GN2:GY19,18,MONTH(TODAY())-1))

How would I do something similar with your solution?

Thank you

Assume B2:M2 houses the 12 month text labels in "mmmm" format: January,
etc
Placed in say, N3:
=AVERAGE(OFFSET(A3,,MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0),,-MATCH(TEXT(TODAY(),"mmmm"),$B$2:$M$2,0)))
would return the YTD average for row3. Copy N3 down as required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
:
I have a previous year I need to average the months on. There are 12
months
of data. I need to compare the average to the number of months so far
for
this year.
For example if in 2007 I had Jan through Dec as 1, 2, 1, 2, 1, 2, 1, 2,
1, 2
etc. and if we were currently in Nov then I want Excel to read the top
of the
column that says "NOVEMBER" and only average up to that column. I
thought of
using MONTH(TODAY())-1 to specify the 11 month and match to the heading
of
NOVEMBER by reconginizing the text (NOVEMBER) with the formula
MONTH(1&L2)
[L2 is the cell that has the text NOVEMBER in it. I just can't figure
out how
to put it all together. I have to manually change the column reference
by
hand each month so the average calculation only looks at the number of
months
up to where we are currently.