Uncharted

[rowyn]

So I have data that looks like this:

Date	Balance
1/1/11	0.00
6/6/11	5,000.00
6/10/11	0.00
7/1/11	50.00
7/12/11	0.00
8/14/11	15,000.00
9/15/11	0.00
12/5/11	5,200.00

The date column is the day that the balance changed. That balance is the account balance until the next date appears.

I want to have Excel do a chart that reflects this: eg, shows the balance is zero from 1/1/11 to 6/6/11, then that it’s $5,000 for four days from 6/6/11 to 6/10/11, then zero again, etc. Now, if I manually add every single day in the year to my data and manually include the balance for each date, I can get Excel to do this. But I would really like to do it without having to make 365 rows of data. x.x

By default, bar graphs will treat my points as if they represent only that day’s data, and that all other days have a value of zero. Line graphs will by default draw a gradual rise and decline over time, which is also wrong.

It seems like this is an obvious sort of problem and there should be an obvious sort of solution to it, but I am just not figuring it out. Anyone know how to do this?

Comments

[terrycloth.livejournal.com]

Have 365 rows with a formula that sets the value equal to the previous row's, then replace the values on the days the data changes with set numbers?
You can have another column with the date to make it easier to find the right dates to enter the numbers.

[rowyn]

I was really hoping to avoid having to convert to a dataset with all dates. c.c But Bard suggested vlookup to find the amounts that went with each date, which is clunky and hard to explain if someone else wants to do it with a different date range, but at least it's quick for *me* to do, yeah. :/