Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Chart (Excel 2000)

    Please see file attached.
    Need to put primary & secondary y-axis cross at zero with x-axis.
    Presently, secondary y-axis is not cross at zero with x-axis.

    Please help to set the following criteria too.
    a) Value for Average Yield Per Sport ($) is max +250 and min is -200.
    [img]/forums/images/smilies/cool.gif[/img] Spots utilisation (%) is positive value for all (max at 75%).

    Please help urgently.

    Thank you very much.

    Regards,
    Rachel

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

    Re: Excel Chart (Excel 2000)

    If I understand your request correctly, it is contradictory.

    If the primary axis runs from -200 to +250 and the secondary axis from 0% to 75%, you cannot have the 0 on both axes in the same position. You'll have to compromize - see attached.

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart (Excel 2000)

    Hans, thank you for your reply.
    But, how to compromize? Any tips?

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

    Re: Excel Chart (Excel 2000)

    See the workbook I attached to my previous reply - I adjusted the minimum and maximum values of both axes to have the same ratio: primary axis -200 to +250, secondary axis -0.8 (-80%) to +1.0 (100%). Since -200 : -0.8 = +250 : +1.0, the 0 is in the same place in both axes. Since the position of the x axis is determined by the primary axis, the secondary axis crosses the x axis in the correct place.

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart (Excel 2000)

    Hans, thanks for the reply.
    Can you advise me how to hide the -ve secondary y-axis as -ve % should not exist.
    Thanks.

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

    Re: Excel Chart (Excel 2000)

    Had to think a bit before I understood what you meant by -ve.

    Unfortunately, we cannot set the minimum value for the secondary axis to 0, for that would shift the axis. The best I can come up with is to change the number format for the secondary axis:
    - Click on the secondary axis
    - Select Format | Selected Axis
    - Activate the Number tab
    - Select the 'Custom' category.
    - Enter <code>0%;</code> in the Type box. This number format consists of two parts: the first is the format for positive numbers, the second part for negative numbers. Since there is nothing after the semicolon, negative numbers will be hidden. I don't know of a way to hide the tick marks for part of the axis.

    I have attached the modifed workbook (zipped).

    Note: there were 5 charts on the worksheet with zero width and height. I removed those.

  7. #7
    New Lounger
    Join Date
    Jul 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart (Excel 2000)

    Hans, thanks very much. You're really great!
    Two more things:
    1) I can see only one chart (not 5 charts) in the attachment.
    2) I don't understand what you mean by zero width and height. Pls help to elaborate.
    Thanks.

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

    Re: Excel Chart (Excel 2000)

    I was trying to get some settings for your charts in Visual Basic and I noticed I got the wrong values. On a hunch, I typed

    ? Activesheet.ChartObjects.Count

    into the Immediate window (in the Visual Basic Editor), pressed Enter and got 7 as result instead of the expected 2. I then typed

    ActiveSheet.ChartObjects(1).Select

    and pressed Enter. Switching to Excel showed that something was selected along the top edge of the sheet, but it was just a single point. I pressed Delete to get rid of it, then repeated these steps until Activesheet.ChartObjects.Count was down to 2 (corresponding to the two visible charts.)

    How the 5 charts got there I don't know. They must have been created at some point.

Posting Permissions

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