J
John McKibbin
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/coaltech/2007_05_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?
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/coaltech/2007_05_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?