target area line graph clolours?

[Former Member]

If possible , how can/would I create a "chart", whereas I would have a line, called "target" and the area of the chart above this line would be say "Green", whilst anything below would be "Red"???

I'm using 2008 Enterprise

[former_member963121 · Accepted solution]

Hi:

Using stacked column chart can meet your need.

Say, there are four data need to be showed on the chart from B1~E1, 20,50,100,120

1. Now we have the target value as 50.

2. To meet the requirement, we need to split the data to two parts, value above 50, values below 50.

3. We need to create two series to show the data.

4. Series 1---- show the data which is less than 50, or the 50 part which is larger than 50.

5. Series 2---- only show data which is more than 50, will show the "data-50"

6. Series 1 cells from B2~E2 should use formula " if(b1>50,50,b1) "

7. Series 2 cells from C2~E2 should use formula "if (b1>50,b1-50,0)"

8. Bind the to series respectively to stacked column chart.

9. Choose the series color under "Appearance-Series".

10. Last for target line, you can add "Horizontal Line" and "Text" component to meet your Target.

I created a demo in my hand, let me know if you need.

[Former Member · Accepted solution]

Hey! Very interesting approach... would this work if the "target" value changed over time, not 50/50 but 10,20,10,25,30,50,30 etc..???

[former_member963121 · Accepted solution]

ha, also interesting requirement, yes, try to please target to a individual cell say A1,

So the other two series cells formula should be changed from

series 1-----"if(b1>50,50,b1)" to "if(b1>a1,a1,b1)"

series 2-----"if(b1>50,b1-50,0)" to "if(b1>a1,b1-a1,0)"

last, place a Gauge or Dial component and bind to "A1", then at run time you can change the "Target Value"

[Former Member · Accepted solution]

Thanks Bill! Worked