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.

I'm really new to Excel...hoping someone could give me some advice. I currently have a table with 2 columns ("Date" and "Score"). I'm trying to keep track of something on a scale of 1-10 on a daily basis. I currently use that data to make a line graph to chart progress. So it's something like:

DATE     SCORE
9/1       5
9/2       9
9/3       10

and so on.

However, I also want to plot WEEKLY averages (the average of a week's worth of scores) on the same graph. The way I'm trying to do it is to create a second chart with "WEEK" and "AVERAGE". So Week 1 would have one score that is the average of 9/1-9/7, for example. The issue I'm having is when I combine the two charts together to form one line graph, the "average" line doesn't take up the whole graph...if that makes sense. Excel aligns each week with one day from the 1st chart so instead of "Week 1"'s plot on the graph being somewhere between 9/1-9/7, it's aligned with 9/1. This effectively makes the 2nd average chart shorter horizontally, instead of taking up the whole graph like I'd like it.

Any ideas? I hope I've made myself clear. Thanks!

share|improve this question

2 Answers 2

up vote 2 down vote accepted

There are a number of different ways to do this, and they'll all differ slightly based upon the type of chart you use and the layout of your data. Here's one simple way to do it:

  1. Layout your data consistently. I've added a third column for the average, and calced it once every 7th day (again, there's many different ways to do this).

  2. Create a Scatter(XY) chart with your Date, daily Score column, and weekly Average column. (you can use line, but I prefer XY's, you'll see why in a moment).

  3. Select your Average series on the chart.

  4. Add Error Bars (Layout > Error Bars).

  5. Delete the vertical error bars.

  6. Format the horizontal error bars:

    • Select Minus direction
    • Set Error Amount to Fixed Value = 7 (number of days the average represents).
    • Set any other formatting you like (color, size, etc...)
  7. Format the rest of the chart to your liking (a good place to start is get rid of all of Excel's standard formatting).

Example chart

share|improve this answer
    
thank you very much! –  pauliwago Oct 7 '12 at 2:31
1  
I think I'd use an error bar length of -6 instead of -7, so that every 7th point doesn't have two reference lines, and there's no confusion about which points go into which average. Even better, use -6.5 and +0.5, so the lines coincide between two points. –  Jon Peltier Nov 27 '12 at 21:29

So I guess what you want is some sort of reference line (the weekly average) in your day-to-day graph, right?

This might not be the fanciest solution, but what about entering as many values for the week average as you have daily data points?

E.g. day: 9/1 9/2 9/3 score: 1 4 7 week avg: 4 4 4

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.