How to draw a polygon in a graph without using VBA

[RobSA]

Hi Folks,


I have a graph that is using known data.


I would like to create an envelope that when veiwed with the line graph the criteria can be seen as met or not met. That is should the line pass over or into the shape then the criteria are met.


I have looked at some example on the internet with no succes yet.


Any advice here would be appreciated


Regards

Rob

 

[Luke M]

you mean something like this?

http://peltiertech.com/WordPress/excel-charts-with-horizontal-bands/

 

[RobSA]

Hi Luke,


Thanks for the reply.


The graph I am trying to reproduce is found at this link http://www.cma.org.za/UploadedMedia/Book2[1].pdf Page 4 Fig.5


. . . Book2 (Open Square brackets)1(Close Square brackets).pdf . . .


Regards

Rob

 

[Luke M]

Hi Rob,

You're link doesn't work for me.

 

[magbo]

Hi, Rob!

You need just a chart for your "envelope":

1st column with sieve grading

2nd - values of lower limit of envelope

3rd - difference between lower & upper limit

then create a chart - layer & cumulative

format 1st series so it has no filling

use grading as category axis etiquettes.

(when I manage to set theproprties to file in google docs, I will share a link)

 

[RobSA]

Hi Luke,


Thanks for the reply.


The link doesnt work as you see it as the reply box will not allow [ ] brackets.


I have thus tried to explain that you must put square brackets around the 1 and then it should work.


I hope this helps


Regards

Rob

 

[Luke M]

Ah, thanks Rob. I didn't understand earlier.

Looks like you could combine an area chart with some sort of XY or line chart possibly. Jon has several ideas here:

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


So, it might be tricky defining the polygon properly, but the overall idea would be to stack some dummy series together and then make certain ones "non-formatted".

 

[magbo]

Hi Rob,

Have a look


https://skydrive.live.com/redir.aspx?cid=f13739371fd5db72&resid=F13739371FD5DB72!103


Edit:

to have a look you need to copy whole link. With exclamation and 103 at the end.

Chandoo's site is great, but this forum's script is a little bit annoying.

 

[anupagarwal06]

This is what you have to do,


1.Select lower limit and Difference column.

2.In office 07+, Insert->Area->2D area->Stacked

3.Now select the lower area, most likely blue and Format->Shape fill->No Fill

Your envelope is ready.

4.Now right click on chart->Select data->Add

5.give your real seive range here in input and click ok. The real data appears as an area

6.Now select the top most area and Design->Change chart type->Line

The result is your chart, adjust Title, Axis and Legends as per your needs.

 

[RobSA]

Hi Magbo.


Thanks for the link and the idea - it is the one that seems to work best.


If you dont mind - please explain how the shading is added ?


Regards

Rob

 

[magbo]

Hi

Shading of a polygon is just automatic excel color for this kind of chart. The only "trick" in this chart is tuninig off color of area below the polygon - right click on the area and select "none" for area color. Or make visible a charting toolbar , select "Serie 2" and set its Properties (icon with hand over paper)