Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter by month & year when I have a column full of dates ?? | Excel Discussion (Misc queries) | |||
average for year | Excel Worksheet Functions | |||
How do you re-code data? Age with decimals into age by full year | Excel Discussion (Misc queries) | |||
Differentiating data by year into columns | Excel Worksheet Functions | |||
Charting a full year without zeros | Charts and Charting in Excel |