Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How to paint area under graph line with average line?

+/- with different colors?

graph screenshot

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:

share|improve this question
    
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

3 Answers 3

up vote 2 down vote accepted

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%.

Column chart using Invert if Negative

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%.

Column chart using two series

share|improve this answer
    
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:

Excel trendline options

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).

As for the color, after adding your trendline:

  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.

share|improve this answer
    
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
1  
@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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.