Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Y-Axis Auto-Scale Min Erroneously Set At Zero
Spent all morning and half the afternoon on the forums and 'net searching for
this answer - hope I haven't overlooked it. I have 95 duplicate data layouts I need to chart...4 different series by month, qtr-to-date and Y-T-D. Each series is structured to contain na() if not yet populated to serve as a placeholder for the actual data as it becomes available. The data consist of index values which, by definition are positive values - the most common value being 100 which is equivalent to average. First chart I created works perfect, deriving the y-axis from the actual data. In particular, the y-axis runs from 85-120, with actual values ranging from 96-115. This is important because the range will be dynamic throughout the year. Subsequent charts (whether created manually or copied from the first and modifying the data range) force the axis minimum to zero, despite the absence of anything close to that or any exceptionally high values. Once again, I could manually establish limits, but I would need to monitor these for 95 charts with each month's appendage of new data. As an aside, I do not have a need for the scales on each chart to match one another. Below I've copied the first two data sets...#1 auto-scales properly, #2 does not. Values for Set #1: Min=85, Max=120, Major=5, Minor=1, CatCross=85; Set #2: Min=0, Max=120, Major=20, Minor=4, CatCross=0. Hopefully you can make sense of the data I've pasted, but one note, there is a blank column between "Dec" and "Q1TD" and another between "Q4TD" and "YTD". This does not seem to pose any problem for the first chart. Thanks in advance for any help! Mike Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Q1TD Q2TD Q3TD Q4TD YTD Set #1 Item 1 98 109 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104 Item 2 103 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 101 #N/A #N/A #N/A 101 Item 3 107 115 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 111 #N/A #N/A #N/A 111 Item 4 96 114 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104 Set #2 Item 1 98 98 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 98 #N/A #N/A #N/A 98 Item 2 101 99 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 100 #N/A #N/A #N/A 100 Item 3 101 110 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 105 #N/A #N/A #N/A 105 Item 4 94 113 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 103 #N/A #N/A #N/A 103 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Y-Axis Auto-Scale Min Erroneously Set At Zero
I suppose it matters what you mean by "properly" scaling the axis. By design
(Microsoft's design, that is), if the minimum and maximum differ by more than about 1/6 of the scale maximum, the minimum defaults to zero. If you're up for a bit of programming and you have a scaling algorithm you prefer, you can calculate your own limits in the worksheet and apply them to the axis: http://peltiertech.com/Excel/Charts/...nkToSheet.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mvagnier" wrote in message ... Spent all morning and half the afternoon on the forums and 'net searching for this answer - hope I haven't overlooked it. I have 95 duplicate data layouts I need to chart...4 different series by month, qtr-to-date and Y-T-D. Each series is structured to contain na() if not yet populated to serve as a placeholder for the actual data as it becomes available. The data consist of index values which, by definition are positive values - the most common value being 100 which is equivalent to average. First chart I created works perfect, deriving the y-axis from the actual data. In particular, the y-axis runs from 85-120, with actual values ranging from 96-115. This is important because the range will be dynamic throughout the year. Subsequent charts (whether created manually or copied from the first and modifying the data range) force the axis minimum to zero, despite the absence of anything close to that or any exceptionally high values. Once again, I could manually establish limits, but I would need to monitor these for 95 charts with each month's appendage of new data. As an aside, I do not have a need for the scales on each chart to match one another. Below I've copied the first two data sets...#1 auto-scales properly, #2 does not. Values for Set #1: Min=85, Max=120, Major=5, Minor=1, CatCross=85; Set #2: Min=0, Max=120, Major=20, Minor=4, CatCross=0. Hopefully you can make sense of the data I've pasted, but one note, there is a blank column between "Dec" and "Q1TD" and another between "Q4TD" and "YTD". This does not seem to pose any problem for the first chart. Thanks in advance for any help! Mike Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Q1TD Q2TD Q3TD Q4TD YTD Set #1 Item 1 98 109 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104 Item 2 103 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 101 #N/A #N/A #N/A 101 Item 3 107 115 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 111 #N/A #N/A #N/A 111 Item 4 96 114 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104 Set #2 Item 1 98 98 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 98 #N/A #N/A #N/A 98 Item 2 101 99 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 100 #N/A #N/A #N/A 100 Item 3 101 110 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 105 #N/A #N/A #N/A 105 Item 4 94 113 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 103 #N/A #N/A #N/A 103 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Y-Axis Auto-Scale Min Erroneously Set At Zero
Jon, thanks a lot for your reply and advice. As I trolled the 'net looking
for a solution, I encountered your website as well as the specific page you have directed me to. I think your approach will work for me just fine. My hope had been that there was something simple I had overlooked in specifying settings that didn't require special handling. I'm still glad I took time to ask the question, because you provided additional detail with respect to how the y-axis scale is determined. If not for your reply, I wouldn't know about the approach Microsoft uses and would continue to be vexed! Thanks again for the extremely valuable help you provide to the community. "Jon Peltier" wrote: I suppose it matters what you mean by "properly" scaling the axis. By design (Microsoft's design, that is), if the minimum and maximum differ by more than about 1/6 of the scale maximum, the minimum defaults to zero. If you're up for a bit of programming and you have a scaling algorithm you prefer, you can calculate your own limits in the worksheet and apply them to the axis: http://peltiertech.com/Excel/Charts/...nkToSheet.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mvagnier" wrote in message ... Spent all morning and half the afternoon on the forums and 'net searching for this answer - hope I haven't overlooked it. I have 95 duplicate data layouts I need to chart...4 different series by month, qtr-to-date and Y-T-D. Each series is structured to contain na() if not yet populated to serve as a placeholder for the actual data as it becomes available. The data consist of index values which, by definition are positive values - the most common value being 100 which is equivalent to average. First chart I created works perfect, deriving the y-axis from the actual data. In particular, the y-axis runs from 85-120, with actual values ranging from 96-115. This is important because the range will be dynamic throughout the year. Subsequent charts (whether created manually or copied from the first and modifying the data range) force the axis minimum to zero, despite the absence of anything close to that or any exceptionally high values. Once again, I could manually establish limits, but I would need to monitor these for 95 charts with each month's appendage of new data. As an aside, I do not have a need for the scales on each chart to match one another. Below I've copied the first two data sets...#1 auto-scales properly, #2 does not. Values for Set #1: Min=85, Max=120, Major=5, Minor=1, CatCross=85; Set #2: Min=0, Max=120, Major=20, Minor=4, CatCross=0. Hopefully you can make sense of the data I've pasted, but one note, there is a blank column between "Dec" and "Q1TD" and another between "Q4TD" and "YTD". This does not seem to pose any problem for the first chart. Thanks in advance for any help! Mike Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Q1TD Q2TD Q3TD Q4TD YTD Set #1 Item 1 98 109 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104 Item 2 103 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 101 #N/A #N/A #N/A 101 Item 3 107 115 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 111 #N/A #N/A #N/A 111 Item 4 96 114 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104 Set #2 Item 1 98 98 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 98 #N/A #N/A #N/A 98 Item 2 101 99 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 100 #N/A #N/A #N/A 100 Item 3 101 110 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 105 #N/A #N/A #N/A 105 Item 4 94 113 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 103 #N/A #N/A #N/A 103 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Update of Y axis Max, Min and Scale | Charts and Charting in Excel | |||
Y Axis Auto updatem Max, Min and Scale | Excel Discussion (Misc queries) | |||
Urgent - X and Y Axis Auto Scale for Chart | Charts and Charting in Excel | |||
Y Axis Auto Scale | Charts and Charting in Excel | |||
Auto Y-Axis Scale | Charts and Charting in Excel |