Solved

Excel - Graph two sets of account balance histories together (dates may vary slightly)

Posted on 2014-09-19
2
177 Views
Last Modified: 2014-09-24
I've got the account transaction data for two different accounts and I'd like to graph them together. Unfortunately, the data isn't daily, it's updated whenever there's a transaction, so while they "share" the key of date, they don't share all the same values for that key. However, there's obviously a lot of commonality

How can I graph these both as separate datasets on the same chart since their key isn't a perfect match. but it similar?
0
Comment
Question by:Ryan McCauley
2 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332542
I would combine them onto a third spreadsheet which does list them by day, and then use SUMIF to copy them into this third sheet, and then do the graph from that.

So you would have columns:
Date
Account 1 Value
Account 2 Value
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40333699
If your x-axis is represented by dates, you can easily plot two separate ranges of data across a date range, regardless of the where/when the data points occur.

If you use a scatter chart (X-Y chart), you can choose each accounts dates and transaction amounts for the X and Y values, respectively and combine the plots with no additional steps.  This example has data from two separate sheets:
XY plot
If, however, you want to plot with a bar/column chart, you'll have to create a third table as Phillip Burton suggested.  One column would have a continuous range of dates and each of the other two columns would have a formula to return that accounts transaction amount for that date.  For example:
combine account informationIf the data is discrete (i.e., one transaction/balance total per day) then you can use VLOOKUP to return the amount, otherwise you'd use SUMIF.  If the source worksheet is titled "Account A" and has dates in column A and the amounts in column B, then the formulas in the combined sheet would be:
B2:  =VLOOKUP(A2,'Account A'!A:B,2,FALSE)   or
B2:  =SUMIF('Account A'!A:A,A2,'Account A'!B:B)

and similarly for Account B
C2:  =VLOOKUP(A2,'Account B'!A:B,2,FALSE)   or
C2:  =SUMIF('Account B'!A:A,A2,'Account B'!B:B)

And a combined bar chart would then look like this:
combined bar chart
Note that these two charts plot the EXACT same data points, so now you have to consider how readable or useful these are to decide what kind to proceed with.  I've attached an example workbook with all this for you to test.

Regards,
-Glenn
EE-CombinedAccounts.xlsx
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
Viewers will learn the basics of using filtering and sorting in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now