Shade feasible region in chart

[jv104]

Hello,
This is probably a simple min/max issue but I have yet to figure it out. My goal is to shade an area below several intersecting lines (feasible region in a group of inequalities). Basically a "fill below" type function but involving several intersecting points (shading below vertices). If you understand linear programming and feasible regions you should have a clear picture of what I'm looking for (intersecting lines, shaded below vertices). Here's an example

. Let me know if you need the actual chart and formulas I am using. As of now the only way I know how to do this is by using a freeform select and fill. I'm looking for a more automated way such as a formula or chart tool method that will mathematically identify the feasible region and shade it automatically, or a manual input into cells or solver that will shade the chart. Can I use the information from the solver to graph the inequalities AND shade the feasible region? In essence, I want to shade "touching" the chart, or shade it when the chart is created. I'm using XL'10. Thanks in advance.

JV

 

[Steve=True]

Check out Jons site where he shows how to shade on many different pages:

http://peltiertech.com/WordPress/fill-below-an-xy-chart-series-xy-area-combo-chart/


Steve=True

 

[Jon Peltier]

This might be easier than messing with an area chart combination.

Here is the data for the three constraint lines, and for the construction of the shading. I put the blocks for constraints A, B, and C side by side because it was convenient for the calculations to follow.

In column A below the constraints table, I listed X values from the min (0) to the max (420) in increments of 10. They start in row 6 and extend to row 48.

In cell B6 I put this formula:

=MAX(($A6-A$2)/(A$3-A$2)*(B$3-B$2)+B$2,0)

I filled this down to B48, then I copied B6:B48 and pasted it into D6:D48 and into F6:F48.

In Cell G6 I put this formula:

=MIN(B6,D6,F6)

and I filled it down to G48. This gives me the coordinates of the lowest Y value at each X value.

Here's how I constructed the shading. The top left chart below shows the three constraint lines.

I copied the shaded range of the calculations (A5:A48, G5:G48). You can select a multiple-area range by selecting the first area, then hold Ctrl while selecting the next area. I selected the chart and chose Paste Special from the Paste button dropdown on the Home tab of the ribbon. This is the set of yellow dots on the top right chart.

I formatted the added series so ti used no markers or lines, but so it had error bars (bottom left chart).

I deleted the horizontal error bars, and formatted the vertical error bars to show minus error bars only with no end cap, using the percentage option, with a percentage of zero. Then I formatted the error bars to use a thicker yellow line. This is what you see in the bottom right chart.