• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Third Dimension Plotting, possible without bubble charts?

E

ewilly

New Member
Thank you so much, Chandoo and community, for a remarkable resource. I have learned an amazing amount and am in peril of knowing "just enough to be dangerous." With that, my question:


I would like to add a few data series with three dimensions of information. I have a combo chart with A) clustered columns showing cash flows over time; B) line chart showing EBITDA:CapEx with percentages on the secondary vertical axis; and I'd like to add a "C" as a bubble chart showing NPV, IRR, and Payback Period. I figure this would work since I have "years" on the primary x-axis, "dollars" on the primary y-axis, and "%" on the secondary y-axis.


However, Excel does not allow bubble charts to be combined with other types. I did use a scatter chart, but had to leave out the sizing of the NPV.


Your thoughts and suggestions would be greatly appreciated!
 
Since the 3 components will most likely have different scales, perhaps a better idea would be a panel chart? You're still plotting against a common time period, but all 3 items can be compared individually, which usually causes less confusion.


Chandoo talks about a few ways to do this here:

http://chandoo.org/wp/2010/05/12/introduction-to-panel-charts-using-excel-tutorial-template/

And there are several other links at bottom of page showing other examples/tutorials.
 
If you set the background of your bubble chart to no colour then you can possibly fake what you're after by placing the two graphs on top of one another, but you'd need to make sure your axis lined up properly (okay if the scales don't change, needs some thought if they do).
 
Luke, thank you for the reply. I agree it would work on multiple charts; however, this chart is for a dashboard where real estate is already at a premium, so that makes panels a sub-optimal option. As to the scaling, the NPV should always be within the hi-lo range of the yearly cash flows (primary y-axis), so that is not a problem.


Ack! it still seems to me that the scatter markers should be able to change size, and dynamically. Well, since I've plotted cumulative cash flows, where they cross the x-axis from negative to positive essentially identifies the payback period (albeit not fractionally to the year) but I guess that is close enough at this point.


Thanks Polar. I thought of that, too, but didn't want the risk the problems with all the changes. Ultimately this will be a template used for several different projects, so the likelihood of scales changing is nearly assured.
 
Ah, understand about real estate. This might still take up too much space, but take a look at the last chart on this page:

http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html

Nice thing with this one is you can change the size of each panel. On my own workbooks, I've set this up with some Forms controls so that I can quickly adjust each size. The overall size of the panel is still "1 chart" with each panel taking up 1/3 of total space.

Bubble charts can mimic a dynamic change...but as you've found out, they don't play well with others, and they don't follow a constant scaling rule, which can distort the data.


Last thought before I go, any of these look viable?

http://chandoo.org/wp/2008/11/14/excel-charting-alternatives-market-share-data/
 
Ewilly


You could also use some VBA code to step through the Scatter Chart series point by point and adjust its size in relation to your data which would emulate what a Bubble chart is doing
 
You must log in or register to reply here.
Back
Top