Beginner's question on adding up and plotting multiple time series

  • Thread starter Thread starter el_truco
  • Start date Start date
E

el_truco

Hello group,

I have the feeling that the answer to this question is going to make
me feel silly, but I really can't seem to work it out using either the
Excel help file or online documentation. I must be looking in the
wrong places!

I have three time series, of three bank accounts. I can plot each
individually without problem, and I can also get one graph with three
lines. So far so good. But what I would like to do is get a graph with
one line, representing the combined amount on the three bank accounts
over time (i.e., adding up the three time series).

Simply adding up the three data series into a fourth one and then plot
it doesn't seem to work because the time points are independent (e.g.,
for one account I have transactions on 2 and 8 December, on another
one on 5 and 6 December).

And in the chart wizard I can't seem to let the time series be added
up.

Any suggestions?
Richard
 
Pivot Table will do it all for you.
Data > Pivot Table > Multiple Consolidation Ranges >
PivotChart Report (with Pivot Table Report) >
Create a single page field for me.
Enter all 3 ranges. Include unique labels.
Finish.
If you want cumulative amounts, right-click on
Grand Total > Field Settings > Show data as >
Running total in > Row
 
Herbert, thanks for this. It *almost* worked, which I consider quite
an achievement given that I had never heard of pivot tables.

Two problems remain:

- As I said in my first post, I want to add up the balances of three
bank accounts over the period of a year and plot them in a graph. My
data points are the balances for each account for every day there has
been a transaction (typically about once or twice a week). These
transactions don't always take place on the same days for each of the
accounts. This means that, for example, account A has transactions
(hence, data points) on 12 and 18 July, while account B has a
transaction on 16 July. Adding up the balances of accounts A and B
doesn't work for 16 July, because on that day the balance of account A
is seen as zero (no transaction, no data point). Whereas in reality
the balance of account A on 16 July is still the same as it was on 12
July.

- There are days when an account has had more than one transaction,
resulting in more than one data point. What the pivot table does is
add up all data values for that day in a particular account, as
opposed to only the final one of the day.

I'm not sure if these are problems that can be dealt with easily, or
whether they perhaps suggest that a pivot table was not the solution
after all...

Cheers,
Richard
 
I think the key is to convert the Sum into Running Totals
as I outlined above.
When you do that, you never get zero balances.
Here is my try at simulating your setup:
http://www.freefilehosting.net/download/3a40d
If you don't want different colors for different accounts,
just label all deposits BankA instead of BankB, BankC
 
Herbert, the example you prepared is absolutely brilliant! Thank you
very much.

I first thought that cumulative totals weren't what I was looking for,
but your example made me realise that I should be using the
transaction amounts ("deposits") as data points, not the resulting
balances.

And as it happens, it also solves my second problem (more than one
transaction on one day).

I'm much obliged!

Enjoy your weekend,
Richard
 
Hi Herbert, sorry to bother you one more time.

Your example spreadsheet would serve the purpose of what I want to do
perfectly, so to save myself some effort I thought I'd simply copy my
dates and transaction values into your worksheets AA, BB and CC, and
then refresh the pivot table. This works fine for the transactions,
but something very strange happens when I copy the dates: the year
becomes 2003 (instead of 2007), and the day becomes a day earlier. For
example, 31-Dec-2007 becomes 30-Dec-2003. I find this very bizarre; I
really haven't got a clue as to what could cause this behaviour.

I do have the cells formatted as dates (both in my original file and
in your example spreadsheet).

Any idea?

Cheers,
Richard
 
Hi Richard,

I hope Herbert doesn't mind me butting in here but the difference
will be due to you using the 1904 date system and Herbert's sheet
using the 1900 date system. You can toggle between the two by
going to Tools>Options>Calculation Tab.

For a full explanation of the two systems type date into the index
box in the help file.

HTH
Martin
 
Back
Top