How to paint area under graph line with average line?

How to paint area under graph line with average line?

+/- with different colors?

Updated:

I need to paint area between graph line and average line with negative(below average line) and positive(above average line) options.

My desirable result will be:

-
can you elaborate a bit more or/and add a screenshot of what you are attempting to do ? – Shekhar Nov 25 '12 at 9:40
please find following graph for your consideration. – Amir Nov 25 '12 at 9:43

How about a column chart. The area chart overemphasizes some points more than the values should allow. Since the points are discrete measurements, the sloping lines between measurements are meaningless, and displaying filled areas is going to be misleading.

Method 1:

Plot column chart. Set vertical axis so horizontal axis crosses at value of 2.8 (or whatever). Format column fill: check Invert if Negative, and select the two colors for positive and negative (which works in 2010, but 2007 is a disaster for setting these colors). Set gap width to whatever looks good; default is 150%, but I used 50%.

Method 2:

Easier, no problems with Invert if Negative. Use formulas in two columns to produce a series for up and one for down. I put the dividing value (2.8) into cell B15. Formulas for column C (up) and D (down) are:

``````C3: =IF(B2>\$B\$15,B2,\$B\$15)
D3: =IF(B2<\$B\$15,B2,\$B\$15)
``````

Select A1:A13, hold Ctrl while selecting C1:D13, and insert clustered column chart. Set vertical axis so horizontal axis crosses at value of 2.8 (or whatever). Format the two series as desired, and use an Overlap of 100%. Set gap width to whatever looks good; default is 150%, but I used 50%.

-
Greetings again Jon. I provided the area chart example for the OP but it was 'photoshopped' from two area charts. I agree column seems better suited here than area - just hope that's OP's opinion! – pnuts Nov 28 '12 at 2:21

Select the graph, and under the `layout` tab of the ribbon, look for trendline:

Select `More Trendline Options...` and choose the settings that you need. for more help, consult this article (the above image is also from that article).

1. Select it
2. Right click and select the last option. It should be Format [line name], it's in dutch for me and I don't know the exact terms (and it's really annoying that you can never find translations easily). Alternatively you can double-click on it.
3. Go to the line color tab
4. Select either the second or third radiobutton. Third is for gradient color.
5. Set the options according to your needs.

Now you should have the line of data you want and the correct color for it.

-
we need to paint area between graph line and average line with negative(below average line) and positive(above average line) options. – Amir Nov 25 '12 at 12:29
@Amir you didn't say that in your question, at least not cleary. I wouldn't know how to do that. – MDeSchaepmeester Nov 25 '12 at 12:44

The process involves creating two separate graphs in one chart, changing the secondary XY axis to fit the second graphs and then removing the excess.

It is complicated and I don't know if it adds anything to the graph. Here is a tutorial from Peltier Tech.

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

-