Value Axis Adjustment

  • Thread starter Thread starter Workbook
  • Start date Start date
W

Workbook

I created a Gnatt Chart with 8 activities. The times on the values axis are
listed as 3:50 AM, 4:19 AM, 5:16 AM, 6:14 AM, 6:43 AM, 7:12 AM, 7:40 AM, 8:09
AM, 8:38 AM, however since my activities take place between 5:30 AM and 8:15
AM, I wanted to decrease the time that is displayed on the chart
dramatically. For example, I would like to see the time on the values axis
displayed in 5, 10, or 15 minute increments. I am assuming this requires me
to adjust the minimum, maximum, major unit, minor unit, and category X axis
data in the Format Axis, but I am not sure what to enter. Could you help me
out with this
 
If you are using Excel 2003 or earlier, you can simply enter the times for
axis scale parameters. For example, min = 5:30 AM, max = 8:30 AM, major unit
= 0:30.

If you are using Excel 2007, you can't do this, because Microsoft broke this
functionality. You will have to figure out the numerical equivalents of
these times. For example, 5:30 = 5.5/24 or 0.2291666, 8:30 = 8.4/24 or
0.3541666, and 30 minutes = 0.5/24 or 0.0208333.

- Jon
 
Hi Jon,

I have the following data in the scale tab of the format axis (found below),
which is what you suggested with one exception being the major /minor unit.
You're recommendation is perfect, exactly what I was trying to do! Thank you
for your help. However now I am having a different problem. The actual bars
on the graph have disappeared when I change the data to what I have below
(same thing happens when the major/minor unit is 30 min). Do you have any
idea of what might cause that to be happening?

Minimum: .02291666 = 5:30 am
Maximum: 0.3541666 = 8:30 am
Major Unit: 0.00347222222 = 5 minutes
Minor Unit: 0.00347222222 = 5 minutes
Category X axis crosses at: 0.2291666 = 5:30 am

Thank you for your time John, I appreciate your feedback.
Ed
 
What were the axis scale settings before you changed them? If the numbers
had dates as well as times, then they would not be between 0 and 1.

- Jon
 
Hi Jon,

The axis scale settings, before I changed them, were as follows.
Minimum = 1.16
Maximum = 1.36
Major unit = .02
Minor unit = .004
Category X axis crosses at = 1.16

You had said "If the numbers had dates as well as times, then they would not
be between 0 and 1." Could you help me to understand better what you mean?

Ed
 
Excel treats dates as the whole number of days since 1/1/1900 (so now we're
somewhere between 39000 and 40000). Excel treats times as a fraction of a
day between one midnight to the next. The original values you just posted
are greater than 1. Probably not intended as a date (2-Jan-1900), but some
other artifact of the recording process.

The values you started with are between 1 and 2. The times that I gave you
and that you applied have values between 0 and 1. Add 1 to make the new
values fit your data.

- Jon
 
Back
Top