If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel 2007 limitations when plotting variable width column charts
I recently discovered a subtle limitation of Excel 2007 when building a
variable width column charts as proposed by Andy Pope (see http://andypope.info/charts/colwidth2.htm). This specific implementation involves building an array of values, charting them as a series of stacked areas, and aligning the stacked areas into columns by setting the horizontal axis type as a date. While this was previously the most versatile and robust solution, new charting limits seem to have been imposed in Office 2007 that limit its implementation. In Excel 2007, the date-formatted x-axis appears to revert the axis type to text upon reaching a maximum axis value of 32760, while Excel 2003 correctly plots the chart up to the maximum value of date formatted data (i.e. 2958466). While this may seem an academic question, such charts are widely applied to generate the cost curves necessary in cost-effectiveness policy studies (see http://www.epa.gov/air/caaac/coaltec...5_mckinsey.pdf page 38), where large magnitudes are required (~500,000 units), while maintaining high resolution (~20-50 units), thus scaling the axis is an inappropriate workaround (as date formatted numbers are stored as integers). Can anyone interpret why this new limit was necessary in Excel 2007 and how I could possibly overcome or work around it? |
#2
|
|||
|
|||
Excel 2007 limitations when plotting variable width column charts
The new limit was necessary only because they didn't realize it wasn't
necessary in prior versions, and they weren't given time to get it right. I've had to fudge with axis scales and dummy series to apply labels. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ John McKibbin wrote: I recently discovered a subtle limitation of Excel 2007 when building a variable width column charts as proposed by Andy Pope (see http://andypope.info/charts/colwidth2.htm). This specific implementation involves building an array of values, charting them as a series of stacked areas, and aligning the stacked areas into columns by setting the horizontal axis type as a date. While this was previously the most versatile and robust solution, new charting limits seem to have been imposed in Office 2007 that limit its implementation. In Excel 2007, the date-formatted x-axis appears to revert the axis type to text upon reaching a maximum axis value of 32760, while Excel 2003 correctly plots the chart up to the maximum value of date formatted data (i.e. 2958466). While this may seem an academic question, such charts are widely applied to generate the cost curves necessary in cost-effectiveness policy studies (see http://www.epa.gov/air/caaac/coaltec...5_mckinsey.pdf page 38), where large magnitudes are required (~500,000 units), while maintaining high resolution (~20-50 units), thus scaling the axis is an inappropriate workaround (as date formatted numbers are stored as integers). Can anyone interpret why this new limit was necessary in Excel 2007 and how I could possibly overcome or work around it? |
Thread Tools | |
Display Modes | |
|
|