Progression-tracking via Excel?

December 5, 2008 1:56 PM

YAEQ: How would I generate a chart in Excel 2007 that tracks, not towards a goal per se, but progress of a value over a length of time?

I'm trying to generate a chart in Excel 2007. I have a spreadsheet with only two columns--one with a date and one with an integer. I'm trying to generate a chart that tracks progress where the integers accumulate, so the sum of the values would be at the top of the value axis. The "history" of values would accumulate towards said sum.

FWIW, sorry if this has been asked before. My Excelfu is weak and I'm not quite sure if I have the terminology before. That said, Google didn't help me very much either. Conceptually it seems easy enough and is probably a stupid function. I'm sorry if I made this more complicated than it actually is.

posted by booticon to computers & internet (8 comments total)

So lemme get this straight: Column A is dates in time, and Column B is different values, like

10/1 42
10/2 12
10/3 2
10/4 45
10/5 31

...right?

If so, you just need a new column next to those two with the values you actually want to chart - the accumulating integers. Assuming this chart starts at A-1, go to cell C-1 and input formula =SUM(B1:B1). Then, click on the tiny box in the lower right-hand corner of the cell, and drag it down to the bottom of the column - that will automatically fill in the formulas relatively. If it doesn't do it correctly, then input them by hand:

=SUM(B1:B1)
=SUM(B1:B2)
=SUM(B1:B3)
=SUM(B1:B4)
=SUM(B1:B5)

Then chart that column.
posted by koeselitz at 2:12 PM on December 5, 2008

Excel calls this an "X-Y" chart; you'd plot time against value.

Be warned, when an axis is time values, Excel does not do anything smart about weeks, moths, quarters, or years, it just sees the date as an integral vaule, so you get very arbitrary tick marsk.
posted by orthogonality at 2:14 PM on December 5, 2008

If I understand you correctly, you want to track a running total over time.

You can add a third column to track the running total by the following:

If your dates start in A1 (and continue in the next row) and your integers start in B1, then in C1 enter "=B1".

In C2, enter "=C1+B2"

Then copy C2 downwards.

You can then graph this running total in column C with the dates in column A.
posted by thisisnotbruce at 2:15 PM on December 5, 2008

koeselitz's solution effectively is the same as mine, maybe a little more elegant.

However, in order to copy koeselitz's formula, you'll want to use anchors like so:

=SUM($B$1:B1)

If you then copy it down, you should get:

=SUM($B$1:B1)
=SUM($B$1:B2)
=SUM($B$1:B3)

etc
posted by thisisnotbruce at 2:17 PM on December 5, 2008

... yeah, it looks like it won't auto-fill correctly. (Arg. Excel sucks.) You'll have to enter them by hand.
posted by koeselitz at 2:18 PM on December 5, 2008

... ah, yeah, thisisnotbruce is right: the anchors will fix the autofill problem. So type

=SUM($B$1:B1)

... and drag the bottom corner down; it'll autofill properly.
posted by koeselitz at 2:19 PM on December 5, 2008

which is exactly what he said to do.
posted by koeselitz at 2:21 PM on December 5, 2008

Oh man, thanks guys. That did the trick.

Also, orthogonality, it looks like it did group by month. Perhaps it's because I have the cells formatted as a date?
posted by booticon at 3:19 PM on December 5, 2008