Adding a target line to an Excel Chart

[daveh]

Hi,

I am creating a chart using a macro, and was wondering whether I could get the macro to automatically add a line across the chart, at a certain point on the Y axis. All I can see in Excel is the functionality to add a trend line, but thats not want I want. E.g. on a scale of 1-5 on the Y axis, I want my users to be be presented with an input box during the chart being created (which I can easily do), and then the target line to be draw straight across the chart at the point they type in (e.g. 4.3).

Any ideas?

Dave.

 

[SkipVought]

Hi,

Get ready for this HI-TECH answer....


Add a column/row of CONSTANT data.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[MeGustaXL]

Hi 2,

Or...

1. Plot a point at X = 0, Y = 4.3 (or whatever User types in)
2. Add an X Error Bar, type 'Plus', with the Custom Plus value set to the Max "X" data value times, say 1.05.
3. Format the Error Bar anyway you like it, ie Red dashed Hairline or whatever.

VOILA! ( Sorry Skip, couldn't resist! )

Chris

Varium et mutabile semper Excel

 

[SkipVought]

doun her' in Texas, we'all say VOLA! ;-)

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[daveh]

Hi Everyone,

Thanks for your help so far. I realise that on the surface this seems pretty simple... but one thing is a problem - this is a bar chart, or ratings (0-5) against a certain category on the X Axis (not quantitative). So therefore, I cannot add a column of constant data, or do (as far as I can see anyway) a X error bar. I appear to be able to do Y error bars, because thats obviously a quantitative set of numbers. But X is just a list of categories.

Dave.

 

[MeGustaXL]

Hi Dave

Try this:

1. Plot a point at X = 0, Y = 4.3 (or whatever User types in)
2. This will be another column in your chart, so Right-Click it and set its Chart Type to X-Y Scatter, Dots.
3. Right-Click the dot and add an X Error Bar, type 'Plus', with the Custom Plus value set to the Number of "X" data values. Tip: You could set up a variable to contain this number.
4. Format the Error Bar anyway you like it, ie Red dashed Hairline or whatever.


Chris

Varium et mutabile semper Excel