Last YTD average in columns with full year of data.
 

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.

Last YTD average in columns with full year of data.
 

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

"Joe" wrote in message
...
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.

Last YTD average in columns with full year of data.
 

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" wrote:
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.

Last YTD average in columns with full year of data.
 

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

"Bob Phillips" wrote:

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

"Joe" wrote in message
...
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.

Last YTD average in columns with full year of data.
 

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

"Max" wrote:

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" wrote:
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.

Last YTD average in columns with full year of data.
 

Why would you need to if the other solution works?

--
__________________________________
HTH

Bob

"Joe" wrote in message
...
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

"Max" wrote:

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" wrote:
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.

Last YTD average in columns with full year of data.
 

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.

"Bob Phillips" wrote:

Why would you need to if the other solution works?

--
__________________________________
HTH

Bob

"Joe" wrote in message
...
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

"Max" wrote:

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" wrote:
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.