Can a Bubble Chart Do This?

[charliecat]

I need a way to graphically show:

• How money sweeps (i.e., moves) from bank account to bank account
• Whether it is an automatic sweep or a manual sweep
• Average dollar amount swept

My initial thought was to create a bubble chart where the size of the bubbles represented the dollar amounts and figure out a way to connect them with solid lines (automatic sweep) or dotted lines (manual sweep). An example is in the attached spreadsheet.

Is there a way to easily do this in Excel? Or should I be using something else?

Perhaps there is a different way to display all of this information that I haven't thought of?

I would create this manually but I’m looking at about 400 accounts. Please help!

[Andy Pope]

You can not mix bubbles and lines.

You could use a xy-scatter with series having different size markers.

I think the hardest part will be coming up with code, I don't think formula will do it, that sorts out the position and size of each sweep plus the direction of the arrow.

What about a bump chart?
http://www.processtrends.com/toc_bumps_charts.htm

From accounts on the left, To account on the right.