3D Surface Graph

[djp203]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to use a 3D Surface Graph to highlight the changes which would occur if a variable changes.
<o></o>
<o></o>
I'm not sure if you need data to help with this, but if you do the data is:<o></o>
<o></o>
Column A, Column B, Column C, Column D, Column E, Column F, Column G<o></o>
.................1%...........2%...........3%.........4%..........5%.........6%<o></o>
5000...........-0.08%.....-0.03%.....0.03%.....0.08%......0.13%.....0.18%<o></o>
10000.........-0.15%......-0.05%....0.05%.....0.15%......0.25%.....0.35%<o></o>
25000.........-0.38%......-0.13%....0.13%.....0.38%......0.63%.....0.88%
<o></o>
<o></o>
(The number of rows is greater than this, next ones are 50000, 100000, 200000, 250000, 300000)


The formula’s in Column B+ all link to another cell outside of this range which changes - the resulting change in the percentages is what I need to be able to highlight in a graph.
<o></o>
<o></o>
I went into the options for the Surface Graph but it seemed pretty limited - I wanted to set up some fixed rules for it (maybe in some form of conditional formatting) where if the percentage is beneath 2.5% then the series is Green, if between 2.5% and 5% then it is <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Orange</st1lace></st1:City>, If above 5% it is red.
<o></o>
<o></o>
I realised that you could change the colour of each series, but as soon as you change the variable all the series parameters change so would have to go through it all again - I almost want to be able to show this graph as a live demo of what would happen<o></o>
<o></o>
Any pointers would be appreciated...<o></o>

 

[Sal Paradise]

You can set the scale of the Y axis to whatever you want and fix it (from -5% to 5% for instance) and then set the major unit to 2.5% and change the axis labels to match your color. Even if you change the parameters, the axis will stay fixed, and so will the colors.

 

[Jobowo]

So what you want is--in a sense--colored layers of the "z-axis" (%)? (Green below 2.5% etc) Can't do this with a chart normally--conditional formatting the cells, which would work, doesn't carry to the data points. The only option is to create a chart macro that scans the data points and assigns color according to value, as you describe:

<code>Worksheets(1).ChartObjects(1).Chart.SeriesCollection(1).Points(1)
(see Point object)
</code>