jeff has attended:
Excel VBA Intro Intermediate course
Setting Chart max and min of custom number axis in Excel 2007
In Excel 2003 it was possible to enter the desired chart axis limits in the format of the axis, this does not appear to be the case in 2007?
For example; In Excel 2003 if the axis number format is mm:ss.0 you can enter “1:30.5” in the minimum or maximum value box, and the chart is scaled accordingly. You can also type in the numeric equivalent which fir 1:30.5 is “0.0010474537037037”. In Excel 2007 it is possible to set the axis value format to be mm:ss.0 but if you enter your desired axis minimum of maximum in mm:ss.0 format it is ignored. The value is only accepted if entered in the numeric format, which is massively painful.
The best solution I have found so far is to find the numeric equivalent of the value you want by switching the cell format between mm:ss:0 and general then copying the numeric value. Is there setting that I have missed? Any help would be greatly appreciated.
RE: Setting Chart max and min of custom number axis in Excel 200
Hi Jeff, thanks for your query. You have hit upon a genuine - and irritating! - problem with Excel 2007! There is, I'm afraid, no built in functionality to solve this, as mentioned here:
http://tinyurl.com/yhf3rv6
"Excel allows you to specify time values for the axis options. Time values are a percentage of a 24-hour day. As such, 11:00 AM is represented as 11/24 = 0.45833. For the maximum value, I’ve rounded this up to 5:00 PM which corresponds to 17 in military time (17/24 = 0.70833). These settings will produce a chart like the following. As you can see, the shape of the scatter chart is very different from that produced by just a line chart.
Those of you familiar with this technique of converting time to a decimal may recall that Excel 2003 allowed you to enter a date and time like “1/1/07 11:00 AM” directly in the axis option min/max fields and Excel would calculate the appropriate decimal representation. This currently does not work in Excel 2007 but will be fixed in a subsequent release."
So no quick way round it beyond enter the times as decimal values I'm afraid. We'll have to wait until Office 2010!
All the best,
Anthony
