Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Rolling chart to show 13 months back from current month
I am using names andd offset to create a 13 month chart which rolls forward
automatically. however it only updates when a new month is added, i want it to display the 13 months up to a particular month - held in a separate cell. currently: x-axis labels uses the name chtCts =OFFSET('Data'!$A$3,COUNTA('Data'!$A$3:$A$27)-1,0,-MIN(chtLenn,COUNTA('Data'!$A$3:$A$27)-1),1) data: =OFFSET(chtCts,0,1) table looks like this: COLA COLB Month Data J 3 F 4 M 5 A 6 M . J . J . A S O N D J So at the moment it displays jan to jan, when F is added to above table, will show feb to feb etc. What i want is to enter a month in a cell, eg M, and for the chart to display 13 months dependent on this, in this example M to M. Have tried countif but for some reason there seems to be alimit to the length of the formula xl2002 will accept as a name. Advice appreciated. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Rolling chart to show 13 months back from current month
You need to change the offset formula, so instead of starting at the last
point and counting backwards, you instead count from the month you select and count backward. In your date column, put a real date, like 1-Jan-09 for last month (use a custom number format of MMMMM to show just the first initial of the month name). Then use a match on a date which is in, say, C1: =OFFSET(Data!$A$3,MATCH(Data!$C$1,Data!$A$3:$A$27)-1,0,-MIN(13,COUNTA(Data!$A$3:$A$27)-1),1) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "PBcorn" wrote in message ... I am using names andd offset to create a 13 month chart which rolls forward automatically. however it only updates when a new month is added, i want it to display the 13 months up to a particular month - held in a separate cell. currently: x-axis labels uses the name chtCts =OFFSET('Data'!$A$3,COUNTA('Data'!$A$3:$A$27)-1,0,-MIN(chtLenn,COUNTA('Data'!$A$3:$A$27)-1),1) data: =OFFSET(chtCts,0,1) table looks like this: COLA COLB Month Data J 3 F 4 M 5 A 6 M . J . J . A S O N D J So at the moment it displays jan to jan, when F is added to above table, will show feb to feb etc. What i want is to enter a month in a cell, eg M, and for the chart to display 13 months dependent on this, in this example M to M. Have tried countif but for some reason there seems to be alimit to the length of the formula xl2002 will accept as a name. Advice appreciated. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Rolling chart to show 13 months back from current month
Thanks Jon. This works fine.
Regards PB "Jon Peltier" wrote: You need to change the offset formula, so instead of starting at the last point and counting backwards, you instead count from the month you select and count backward. In your date column, put a real date, like 1-Jan-09 for last month (use a custom number format of MMMMM to show just the first initial of the month name). Then use a match on a date which is in, say, C1: =OFFSET(Data!$A$3,MATCH(Data!$C$1,Data!$A$3:$A$27)-1,0,-MIN(13,COUNTA(Data!$A$3:$A$27)-1),1) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "PBcorn" wrote in message ... I am using names andd offset to create a 13 month chart which rolls forward automatically. however it only updates when a new month is added, i want it to display the 13 months up to a particular month - held in a separate cell. currently: x-axis labels uses the name chtCts =OFFSET('Data'!$A$3,COUNTA('Data'!$A$3:$A$27)-1,0,-MIN(chtLenn,COUNTA('Data'!$A$3:$A$27)-1),1) data: =OFFSET(chtCts,0,1) table looks like this: COLA COLB Month Data J 3 F 4 M 5 A 6 M . J . J . A S O N D J So at the moment it displays jan to jan, when F is added to above table, will show feb to feb etc. What i want is to enter a month in a cell, eg M, and for the chart to display 13 months dependent on this, in this example M to M. Have tried countif but for some reason there seems to be alimit to the length of the formula xl2002 will accept as a name. Advice appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I show the current month in a date field? | Excel Discussion (Misc queries) | |||
In a range of months can I capture the most current month entry? | Excel Discussion (Misc queries) | |||
HOW TO SUBTRACT A YEAR+MONTH AND SHOW DIF IN MONTHS? | Excel Worksheet Functions | |||
3 month rolling chart | Charts and Charting in Excel | |||
Graphing past months (totals) and current month (weekly total) in | Charts and Charting in Excel |