Graphing Problems With Excel 2003

Welcome to the Board

I don't know if this is exactly what you want, but may be worth a look at:

http://peltiertech.com/Excel/Charts/BackgroundFill.html

HTH

Boy - this was interesting....

I think I have a solution that works, but I can't get the HTML Maker to work on this machine. I will post an HTML picture later tonight from home.

The approach is to create three new series, one for the values above the target, one for the values below, and then one for the target value. The "above" and "below" values are easy - they are just a max or min function applied to the actual value and the target. When these are plotted in different colours we get close to the desired effect, if the "target" series is "on top" or foremost (the bottom series listed in the series order dialogue). Unfortunately, it is "close but no cigar" - the above and below series diverge when they cross the target value and look kind of flakey.

The solution (messy as it may be) is to interpolate a new point that will lie on (or actually, under) the target line, forcing each high and low series to include that same value so they will 'join' as the composite line passes through the target value. If the adjacent values of the "above" and "below" series are not equally above and below the target value then this 'crossover point' will not be mid-way between them, so we have to control the horizontal positioning of this synthesized point (the X-value) - which implies that we need an XY scatter chart.

The generalized situation is a chart where the observed value may cross the target value several times, and where the target value may change - that means that we might have to interpolate between every successive data pair. We run into a problem, though, since if the successive data pairs do not cross the target value we don't want to insert a calculated value coresponding to Y=Target and X = some interpolated value - so we will have to test for that on each calculation.

We start with actual X and actual Y values in two columns - in the formulas below the actual X value are in column C, and the actual Y values are in Column D. We start by inserting a new row between each successive data pair. We create columns of "synthetic X" and "synthetic Y" values: these are the values that will actually be plotted. For each row where we have an actual X or Y value, the formula is just "= X value" or "= Y value." In the blank rows we calculate either an interpolated value that represents the crossover point, or just a point at the mid-point of the line segment that would join the two successive real values. These interpolations are based on the prior and subsequent data values (ie, the row above and the row below. For the X values (in this case, in row 6) the formula is: (note that I have defined name of "Target" where I can enter and change the target value).

The formula first tests whether the two data points lie on opposite sides of the target value by comparing if the coresponding Y values are either greater/lesser or lesser/greater, respectively - that's all the logic tests and comparison operators. If the Y values are either both >= or both <= the Target value then it returns the average of the actual X values on either side - if the Y values one greater and the other lesser then in interpolates a new X value.

The synthetic Y values are calculated similarly: The logic is similar, except that we know that the Y value for the crossover point is the Target value, so the returned value is either the average of the Y values of the successive ata points or the target value, and no interpolation is required.

Finally, we have a (synthetic) Target value series that is just set equal to "Target" throughout - plotting these three synthetic data series will give us a three-colour chart that (I think) does what you want. Note that they should be plotted as an XY chart, with the "smoothed line" option turned off.

I promised (or was that a threat? :wink to post a picture of the spreadsheet and calculations. It turned out to be easier to provide a full spreadsheet sample, and I have done so at another location that allows file attachments.

Andy Pope has posted a very similar solution on his web site:

http://andypope.info/charts/conditionalline.htm

Hi Jon - yes, it is very similar, at least in concept, although Andy's relies on VBA to update the chart rather than a formula approach. I had never seen that before, but there are only so many ways to force line segments to overlap or align, of course...

Dean -

I haven't checked out Andy's example in some time, so of course I didn't remember it relied on VBA. In fact, I've used a formula based technique like yours on many occasions. In general, if I can do something with formulas instead of VBA, I prefer that technique.

Jon -

I just looked at it quickly, but Andy's solution uses a UDF to solve for the crossover points for each line segment (or return an N/A indicating that there is no crossover) given a "target" line. It then then pastes the required "real data" and crossover values into the plotted ranges on demand with VBA. One advantage is that the target value can change over the domain, rather than remaining static - so it can be a sloping line. Now the generalized solution would be to allow it to change in increments, so it could be an approximation to a curve... or just look for software that is intended for technical graphics!

The formula approach could also work for a non-constant target. I think I've done it this way in a proprietary solution for one client. The formula just has to be a bit smarter.

No - the the guy who writes the formula has to be a bit smarter...