# Thread: Excel Chart (Excel 2000)

1. ## Excel Chart (Excel 2000)

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.

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%).

Thank you very much.

Regards,
Rachel

2. ## Re: Excel Chart (Excel 2000)

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. ## Re: Excel Chart (Excel 2000)

But, how to compromize? Any tips?

4. ## 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. ## Re: Excel Chart (Excel 2000)

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

6. ## 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. ## 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. ## 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
•