Shade feasible region in chart

jv104

New Member
Joined
Nov 10, 2011
Messages
1
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
321-sa9.gif
. 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
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

FillBelowErrorBars1.png


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.

FillBelowErrorBars2.png
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,081
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top