3

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!

Improve this question
1
  • 1
    The accepted answer is an excellent one. In case someone else is looking for a different presentation, you could also add a trendline and switch it from linear to moving average with a period of 7. This will give you a 7 day average recalculated each day, rather than a Mon-Sun average all week long.
    – Adam
    Commented Aug 6, 2015 at 17:17

2 Answers 2

Reset to default
3

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

Improve this answer
1
  • 3
    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
    Commented Nov 27, 2012 at 21:29
0

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .