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

[dajagr.livejournal.com]

By playing around with things a little, I was able to get something similar to what I think you wanted. It takes a little bit of extra effort in your rows, but nothing too horrible (I hope). Here's the result I came up with:

(http://s45.photobucket.com/albums/f80/djagir/?action=view&current=balance.png)

What I did was to duplicate each entry with a corresponding "close" date, something like this:

You still have to enter twice as many rows, but it's not as much as 365 rows for every year, at least. Of course, that 50 is just an invisible little blip in this chart, but you can probably play with the axis if that becomes an issue. (0 makes logarithmic axes unhappy, but I'm sure some tweaking can get you something useful.)

[rowyn]

Oh, that's clever! Thanks. :) I think I'll end up with 365 ros and vlookup to determine which date goes with which number from the original dataset. It's clunky but easy to automate, at least, for future uses. :/