Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Floating Bar Chart lookin' screwy (2003 SP2)

    I need a floating bar chart that represents any number of individual projects along a quarterly timeline. I've specified the dates starting no earlier than 1/1/08, but for some reason the hidden bars start in August of 07. I'm attaching a file. Can anyone enlighten me?
    thanks!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    Excel tries to determine the min and max values for the value axis (in this chart the horizontal axis) automatically. In mnay situations this works well, here the choice isn't exactly what you want. You can force the axis to begin on January 1, 2008:
    - Double click the horizontal axis.
    - Activate the Scale tab.
    - Change the Minimum from 39300 to 39448 (the numeric value that corresponds to 1/1/08).
    - Click OK.

    How can you find the numeric value for a date?
    - Enter the date in a cell.
    - Make sure that the cell is selected.
    - Select Edit | Clear | Formats.
    - Note the number in the cell.
    - Select Edit | Undo (or press Ctrl+Z, or click the Undo button on the toolbar) to restore the date format.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    Thanks Han, this worked perfectly.

    Why would Excel try to estimate anything? Where's that value coming from?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    I don't know the exact algorithm Excel uses, but it tries to use "nice" values for the Minimum and Maximum values of the axis, in this example multiples of 100.

    Dates are stored as the number of days since December 31, 1899, so for example January 20, 1900 is stored as 20. January 1, 2008 happens to be 39461.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    Couldn't I have just supplied a minimum value? Perhaps this was missing from the original tutorial. That's how it came - I lifted the sample from a web page.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    The minimum was set to automatic in the sample workbook you attached: the check box next to Minimum was ticked. When you set the minimum to a specific value, you'll see that the check box is cleared. If you tick the check box again and click OK, you'll find that Excel calculates the minimum automatically once more (the chart will start in August 2007).

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    The algorithm XL uses for determining the AUTO values can be seen at XL2000: How Chart Axis Limits Are Determined

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    Thanks, Steve.
    Let's see how this applies to Naomi's chart:
    yMin = 01/01/2008 or 39448.
    yMax = 08/30/2008 or 39690.
    The difference between yMin and yMax is only 0.6% of yMax, so the minimum value for the axis is calculated as yMin - ( ( yMax - yMin ) / 2 ) = 39327, and this is rounded down to the nearest multiple of 30, the major unit for the axis. This is 39300, which was indeed the minimum value.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    Were you expecting it not to work? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Floating Bar Chart lookin' screwy (2003 SP2)

    Thank you, guys. I don't have time at the moment to trudge through the formula, so I am glad that Excel does work!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •