Shading Area Between Two Line Charts and Axes

2
votes

I am on day #2 of searching the web and, while I have found plenty of hits that seem like they should work, none of them seem to apply to my particular situation.

I have an Excel chart with two series displayed. One is a sort of exponential decay curve, and one is a constant that intersects with the exponential curve, but does not continue past it (the final x-value of the orange line is estimated to make it look like it intersects the blue curve):

enter image description here

The raw data for the blue curve is as follows (leaving off data labels for confidentiality reasons, but x-values are on the left and y-values are on the right):

enter image description here

The orange line is simply set at 24 all the way across until it intersects with the blue curve.

So here's the problem I need to solve: I need to fill in all of the area below the blue curve with one color, and I need to fill in the area below the orange line with another color. Everything above the blue curve needs to be blank (transparent). Here's an illustration of what I want:

enter image description here

I know in order to get the coloring/shading I need to use an area chart. However, when I try to change the chart type to Area the scales of the axes change for each series and they no longer match up, and I am unable to edit the axes (can't set min, max, etc) to make them match up again. Additionally, only the area directly beneath the constant line fills in (as expected), but I am looking for a way to fill in the area between the orange line, the blue curve, and the axes:

enter image description here

How might one go about doing what I need to do?

If there's any other information I could provide that would be of help, please let me know and I'll be sure to add it in.

EDIT: I can extend the orange line to follow the blue line off to the right, which may help fill in the lower area. However, when I switch to an area chart I still get the issue with mismatched axes with scale I can't edit:

enter image description here

Notice how the "567" point (the x-value where the orange line should intersect the blue curve) is spaced evenly between "500" and "600", rather than scaling slightly to the right of center as I would have expected.

How do I keep the spacing of one tick every 100 units on the x-axis but keep the datapoint for 567?

2
excelchartsfillarea
Well excel did exactly what you asked it to do, colour the area beneath the curve.... – Luuklag
@Luuklag Understood, and I don't fault Excel for it at all. I'm simply looking for a way to have Excel do what I need using existing tools. – DerStrom8
I would just have two charts : one does the blue and the other the orange then lay the orange chart on top of the blue and make it transparent ... worked a treat in the past... – Solar Mike

2 Answers

1
votes

You could find the intersection point's coordinates (graphically or analitically), then split your data in two separate series within the same graph as follows :

enter image description here

Edit post comment section :

For some reason x-values are considered by default as text.

Righ click the x-axis > format > Select date on the axis

Then play with the principal and base in days/months to have the intervals you want.

Good parameters for this data :

  • main : 100 in days
  • base : in days

enter image description here

0
votes

I would just have two identical charts : one does the blue and the other the orange then lay the orange chart on top of the blue and make it transparent ... worked a treat in the past...