Shading Between Intersecting Lines

[Jeff Zahn]

Excel 2000 problem:

User requested a chart that plots two lines
X is a series of dates
Y1 and Y2 series are two interest rates on said dates, expressed as %

Plotting this as XY as Line is no problem. About half way across the
chart, the lines intersect and the higher one becomes lower. Picture a
bow tie.

(Best viewed in a fixed with font)

-------- ---------
A \/ B
/\
-------- ----------

Here is the problem: how do I shade the area BETWEEN the lines in one
color before they intersect and in a different color afterwords. Area A
and Area B need different colors.

I ended up using Area charts, but I had to do a lot of tricks and
create extra series for the MAX and MIN of the two original series, and
overlaid a white Area series at the base, then play around with the
series order to get it all to work. I'm still not satisfied with my
results.

Is there a simpler solution?

Thanks for any help or tips.

--
Jeff Zahn

[Jon Peltier]

Jeff -

I don't know how tricky it really was, but if the two
lines cross at a point where you have values for Y1 and Y2
(i.e., at one date, Y1=Y2), this works.

Data in three columns: A - Date, B and C - Y1 and Y2.
Column D is the MIN of B and C, Columns E and F have these
formulas:

Cell E2: =If(B2>C2,B2-C2,0)
Cell F2: =If(B2<C2,C2-B2,0)

Now make an area plot using columns A, D, E, and F.
Format the MIN data to have no fill, so all that shows is
the two sides of your bow tie.

If this is what you call "a lot of tricks", then welcome
to Excel! (Just kidding. If you know about this news
group, you're probably the local expert.)

- Jon

>.
>

[Jon Peltier]

Jeff -

If the lines intersect where there is no point, this
technique is unsatisfactory. I can give you a chart with
the proper crossing, with the area between the lines
filles, but the same color on either side of the
intersection.

Column A: Dates (X)
Column B: Y1
Column C: Y2
Column D: Y2-Y1

Make your area plot with series defined by Y1 and by Y2-
Y1. Set the fill color of Y1 to None, and the result is a
monochromatic bow tie.

- Jon

>.
>

[Jeff Zahn]

Jon Peltier wrote:
>
> Jeff -
>
> If the lines intersect where there is no point, this
> technique is unsatisfactory. I can give you a chart with
> the proper crossing, with the area between the lines
> filles, but the same color on either side of the
> intersection.
>
> Column A: Dates (X)
> Column B: Y1
> Column C: Y2
> Column D: Y2-Y1
>
> Make your area plot with series defined by Y1 and by Y2-
> Y1. Set the fill color of Y1 to None, and the result is a
> monochromatic bow tie.
>
> - Jon

Thanks for the help, Jon. But the lines intersected where there was no
point. However, after playing with it for a while, I figured out a
solution, which I am now happy to share with the group.

Here's the original problem:
=====================================

User requested a chart that plots two lines
X is a series of dates
Y1 and Y2 series are two interest rates on said dates, expressed as %

Plotting this as XY as Line is no problem. About half way across the
chart, the lines intersect and the higher one becomes lower. Picture a
bow tie.

(Best viewed in a fixed width font)

-------- ---------
A \/ B
/\
-------- ----------

Here is the problem: how do I shade the area BETWEEN the lines in one
color before they intersect and in a different color afterwords. Area A
and Area B need different colors.

=======================
Solution:

Even though the chart was more complicated than the sketch above, at
least the dates (X values) were evenly spaced. So I figured out that
they had to intersect halfway between the data points.

So I inserted a NEW DATA POINT to represent the intersection. For the
Date (X) and data (Y1 an Y2) series, inserted a formula that added the
values before and after (above and below) and then divided by 2, giving
the midpoint. The MIN series was not changed.

Plotting Y1, Y2, and MIN (in that order) as overlaid Area charts
worked perfectly. MIN is formatted with White fill and no border, to
match the plot area fill we use at work.

If anyone needs so see an example of this, e-mail me and I'll send you
an XL97 workbook with my solution. It should work in all versions.

Woo Hoo!

--
Jeff Zahn

[Jeff Zahn]

Hi Jon,

You wrote:
> If this is what you call "a lot of tricks", then welcome
> to Excel! (Just kidding. If you know about this news
> group, you're probably the local expert.)

I already posted my solution to m.p.e.c, but thanks for taking the time
to respond.

And I AM the local expert (at least, one of them). I am a "Design and
Technical Coordinator" (read "operator/floor support") for the
Presentation Center at a Major Investment Bank. I find the newsgroup
very helpful in finding solutions to tricky problems. When I can, I
share my knowledge.

Thanks, again,

--
Jeff

[Jon Peltier]

Jeff -

I think you're lucky that the points intersected at the
midpoint of the interval. I was starting to develop an
interpolation routine to figure the point of intersection
and insert a new point, but it got complicated to test
which two points the intersection fell between, and, well,
this is volunteer work.

I liked the no fill of the MIN series better than a blank
white fill. The blank white would cover up gridlines and
the like. Unless there's something you're hiding in your
solution that I didn't have to hide in mine.

- Jon

[S...@internet.org]

Here's a variation on Jon's:
1) It uses XY Scatter instead of Area charts
2) It plots all the ranges in columns A to E
as follows:
X-axis: A
Y-axis: B (Series1) and C (Series2)
Negative Error Bars for Series1: D
Negative Error Bars for series 2: E

Not the same as using area charts for well-behaved data,
but better than area charts for data that do not cross naturally.
I think of it as a striped bow-tie!!

This takes care of any non-mid-term intersections of the
two interest rate lines, which Jeff assumed incidentally in his
solution as I read it.

Hope I have not missed anything!

- Sam

"Jon Peltier" <jonpe...@yahoo.com> wrote in message news:1471101c13162$acc4f6f0$a5e62ecf@tkmsftngxa07...

[S...@internet.org]

Any comments on my solution, Jon?
-Sam

"Jon Peltier" <jonpe...@yahoo.com> wrote in message news:1a31c01c1318a$f46174c0$9ae62ecf@tkmsftngxa02...

[Tushar Mehta]

The 'shading' is rather sparse, restricted as the error bars are, to the
number of actual data points.

I've spent the last hour or so trying to write a named formula that will
do the following.

Assume a set of numbers in, say, A1:A10, and 'number of slices' in, say
C1. Now calculate values using linear interpolation as in the pseudo-
array-formula
A1:A9 + (ROW(1:nbr_slices+1)-1)*(A2:A10-A1:A9)/(nbr_slices+1)

Plot this named formula.

So, if anyone can create the necessary named formula...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--

In <euUIY6ZMBHA.2028@tkmsftngp02>, S...@internet.org <S...@internet.org>
wrote

[Jon Peltier]

Tushar -

I was starting also to think of a VBA solution which worked in similar
fashion, but then I thought, what if the lines cross several times. I took a
couple advils and I'm feeling better now.

- Jon

In article <MPG.15f88953...@msnews.microsoft.com>, Tushar Mehta
said...

[Tushar Mehta]

If the OP can live with the same color in all zones, your (Y1, Y2-Y1)
solution works just fine. By visual inspection it works for any
combination of values and multiple intersections. I tested it for

1 2
1 2
1 2
5 1
4 2
3 4

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--

In <1a31c01c1318a$f46174c0$9ae62ecf@tkmsftngxa02>, Jon Peltier
<jonpe...@yahoo.com> wrote

[Tushar Mehta]

If the OP can live with the same color in all zones, your (Y1, Y2-Y1)
solution works just fine. By visual inspection it works for any
combination of values and multiple intersections. I tested it for

1 2
1 2
1 2
5 1
4 2
3 4

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--

In <1a31c01c1318a$f46174c0$9ae62ecf@tkmsftngxa02>, Jon Peltier
<jonpe...@yahoo.com> wrote

[S...@internet.org]

Tushar,

Thanks for your observation.

Granted that the density of error bars is limited to the number
of data points available, the intent of the presentation is to
indicate where one interest rate curve is above the other,
and vice-versa. Sparse as it may be the message is not lost
due the sparsity of "bars".

Another approach is to add a denser series overlayed with its
error bars as a proxy for the actual data (after rigorous testing),
of course. Is that the approach you have taken in your
pseudo-formula?

In general, and it does happen quite often, people do get
carried away with formatting and lose sight of the ultimate
goal - making a chart simple enough to understand. All of us
vary to on that regard. For the chart under discussion, does the
solid shaded region between the interest curves add significantly
to understanding what is happening with those rates when the
lines themselves could be colored appropriately. Perhaps if the
curves were of the same color and one wished to distinguish
between them; however, I do not think that is Jeff's case.

Besides I did not wish to spend too much time on this problem
yesterday - that was a trial for my OC nature when it comes to
solving puzzles!!

- Sam

"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message

[S...@internet.org]

Tushar,

Your formula is closely related to:
=G1+(ROW(INDIRECT("$1:$"&$F$1+1))-1)*(G2-G1)/($F$1+1)
which, when F1 = N, generates (N+1) uniformly spaced
intermediate values between G1 and G2. With G1=1, G2=2,
and N = 9, the above formula generates an array {1;1.1;1.2; ...;1.9}.

I was going to use the above to generate abscissae between
each pair of raw data point abscissae, and then use them to
generate piecewise (or pairwise) interpolated values. The first
cut appears to be to create a set of these interpolated values
for each pair of points using a pair-specific defined formula.
So for M pairs of points we would have M interpolating series
that would be plotted, say on an XY chart, and the lines formatted
using the same color index.

I tried a revised version of your formula, but it did not yield what
I expected, which was interpolated x-values between successive
raw data points whose error bars would be used in fill the space
generated by solution to Jeff's problem.

=A1:A9+(ROW(INDIRECT("$1:$"&(Nbr+1)))-1)*(A2:A10-A1:A9)/(Nbr+1)

- Sam
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message news:MPG.15f88953...@msnews.microsoft.com...

[Jon Peltier]

Another possibility just occurred to me, and it's one that
I used on a project a year or so ago. Draw the lines, but
use a line chart, not an area chart. Construct another
set of series to create the fill effect. For this I used
stacked columns on a secondary axis. The individual
columns are made very thin, so you can't resolve the step
between adjacent columns, with a fill color but no
border. It looks like the introductory description of
area calculation by integration from your first semester
calculus text. An unfilled column series is used for MIN
(Y1,Y2), then one fill color for Y1-Y2 if Y1>Y2, and
another for Y2-Y1 if Y2 is greater. Values are computed
by interpolation between the actual data points on the
line charts.

- Jon

>.
>

[Tushar Mehta]

Yeah, I tried something like that yesterday. Too much work and it still
requires the computation of *many* interpolated points between each pair
of specified points.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--

In <1a2da01c1321c$9e9cbdf0$b1e62ecf@tkmsftngxa04>, Jon Peltier

[Jon Peltier]

The calculations aren't bad, just set up formulas in a worksheet, stuff like
if(B2>A2,B2-A2,0) and so on. I had made one like this with around a thousand
points, so you couldn't see the zigzag along the edge of the colored area.

In article <MPG.15f9927ad...@msnews.microsoft.com>, Tushar Mehta
said...

[Jon Peltier]

Okay, boys and girls -

I dug out the workbook I constructed last December, just to see how I had
done it. Manually I inserted a line to account for the point of
intersection, then had a formula compute the date at which the lines cross,
which was the X coordinate of the intersection. This enabled two different
colrs for the area fills, plus the invisible MIN region below them.

If anyone wants to see the chart, email me outside the news group and ask for
JoanChartShadingIIa.xls. Joan liked it, and her boss was really impressed
(I'm not sure that she didn't take credit for it, but what the hey).

- Jon
_______

In article <1a31c01c1318a$f46174c0$9ae62ecf@tkmsftngxa02>, Jon Peltier
said...

[Jeff Zahn]

In advance, thanks to all (Jon, Sam, Tushar) who spent their Brane cells
and time working on this problem. I have read the entire thread to date
and think that some comments are necessary to further define the problem
for those of you who might be still working on it.

S...@internet.org wrote:

> Granted that the density of error bars is limited to the number
> of data points available, the intent of the presentation is to
> indicate where one interest rate curve is above the other,
> and vice-versa. Sparse as it may be the message is not lost
> due the sparsity of "bars".
>
> Another approach is to add a denser series overlayed with its
> error bars as a proxy for the actual data (after rigorous testing),
> of course. Is that the approach you have taken in your
> pseudo-formula?

> In general, and it does happen quite often, people do get
> carried away with formatting and lose sight of the ultimate
> goal - making a chart simple enough to understand. All of us
> vary to on that regard. For the chart under discussion, does the
> solid shaded region between the interest curves add significantly
> to understanding what is happening with those rates when the
> lines themselves could be colored appropriately. Perhaps if the
> curves were of the same color and one wished to distinguish
> between them; however, I do not think that is Jeff's case.

Yes, I agree that we sometimes get carried away with the formatting
issues rather than focusing on a clear presentation of the data in a
graphical format.

In this particular case, our Investment Bank was trying to demonstrate
to the client the advantages / disadvantages of two different bond
scenarios over a ten year period with and without the influence of
Government Income Taxes.

The dates involved in the input data were all 6 months apart, which
represented the way this fixed-income security (bond) was structured.

Both scenarios the proposed bond would increase its coupon rate
significantly in years 5-10. Hence the lower line crossing to be above
the higher line.

Without regard to Income Tax (which the client does not currently pay,
since they are running at a loss), we wanted to show that the Area A
(before the rate change) was equal to the Area B (after the rate change
until the bonds were retired). Hence, a benefit of 0.

However, with Income Tax included (which the client forecasts they would
start paying in 4 years) we wanted to show that the Area A was greater
than Area B (a benefit of >0) to client with a different coupon
structure.

That's why it was important to color the two areas differently.

I know this is all vague, but I was asking for help on a specific
problem with a specific set of circumstances. And you all helped.

If I tried to introduce concepts such as "area under a curve",
"integration" and "numerical methods" in my workplace I would be tarred
and feathered. The solution I found sufficed for our needs, only
because the dates for rate changes were evenly spaced at 6 month
intervals over 10 years.

Thanks again to all, and I owe you Tylenol.

Regards,

Jeff Zahn

[S...@internet.org]

Jeff,

Note: In your solution posted earlier in the thread, you
calculated a midpoint value; however, your rate curves are
not guaranteed to intersect in the middle of a six month
period - are they? You probably need a more general
approach to estimate the intersection point.

Presumably, with 10 years and 6 month periods the XY
chart with error bars approach may have been a
satisfactory solution to your problem.

Thank you for sharing the details of the problem you
were solving.

-- Sam

"Jeff Zahn" <jz...@pipeline.com> wrote in message news:3B95E337...@pipeline.com...

[S...@internet.org]

Another throught, with the data points you had, and for
the purpose of demonstrating relative levels of benefit,
you probably could have computed the area between
the curves and then displayed the regions A and B and
the rate curves suing a combination scatter line-column
chart. The relative benefit of one scenario over the other
would be more readily apparent with the columns than
with shaded areas, where the latter have the potential to
lead to an unwarranted inference if the areas were of
anything other than starkly different sizes.

-- Sam

"Jeff Zahn" <jz...@pipeline.com> wrote in message

<SNIP>

[Jon Peltier]

Hey Jeff -

Given your intent, to show a kind of cumulative payoff,
you could even have shown floating bars, with one bar at
each six month point. This is probably easier and no less
illustrative than the area charts we've wrestled with here.

It is easy to make two sets of bars, one for gain and one
for loss. I think I would add two line charts, one for A
and one for B, with the floating bars connecting them.
You'd have to do the lines on the primary axes and the
bars on the secondary, but that's a detail.

- Jon

>.
>

[Jeff Zahn]

Jon Peltier wrote:

> Given your intent, to show a kind of cumulative payoff,
> you could even have shown floating bars, with one bar at
> each six month point. This is probably easier and no less
> illustrative than the area charts we've wrestled with here.
>
> It is easy to make two sets of bars, one for gain and one
> for loss. I think I would add two line charts, one for A
> and one for B, with the floating bars connecting them.
> You'd have to do the lines on the primary axes and the
> bars on the secondary, but that's a detail.
>

I agree with you.

As it turns out, after all our hard work, the @#$%ing Banker decided to
delete the pages containing these charts from the book. It's a
frustration to struggle with a problem because they ABSOLUTELY NEED TO
display a graph a certain way, then to have them cavalierly discard your
work. But that's part of the territory.

Ah, well...

--
Jeff "Grrr!" Zahn

[Jon Peltier]

Jeff -

You know the two rules:

1. The customer is always right.
2. When the customer is wrong, see Rule 1.

- Jon

>-----Original Message-----
.....

>
>As it turns out, after all our hard work, the @#$%ing
Banker decided to
>delete the pages containing these charts from the book.
It's a
>frustration to struggle with a problem because they
ABSOLUTELY NEED TO
>display a graph a certain way, then to have them
cavalierly discard your
>work. But that's part of the territory.
>
>Ah, well...
>
>--
>Jeff "Grrr!" Zahn

>.
>

[Jeff Zahn]

Jon Peltier wrote:

> You know the two rules:
>
> 1. The customer is always right.
> 2. When the customer is wrong, see Rule 1.

You are so right. In this case, though, the "customer" still ends up
paying about $100/hour for our time, whether they use the finished
product or not. Those who habitually waste time and money are the first
ones up against the wall when the (inevitable) next round of staff
reductions happens.

--
Jeff "And they're also wasting VALUABLE RESOURCES" Zahn