Stacked waterfall with multiple series

M1985

New Member
Joined
Jan 15, 2014
Messages
5
Hey,

I am trying to construct a stacked waterfall that supports:
  • Multiple series
  • Can go below zero

I have followed the guide on peltiertech (http://peltiertech.com/WordPress/excel-waterfall-charts-bridge-charts/)
Stacked Columns for Positive and Negative Data and managed to built the chart for one series.
However, when I try to expand the chart and formulas to incorporate several series I run into trouble.

The "difficult" part is that every series can be split on the x-axis and I would assume that I need 4 columns (up>0, up<0, down>0, down<0) for each series.

Case1:
Start 100
Series1 -50
Series2 -75

Here we should have:
Series1 (Down>0):-50
Series2 (Down>0):-50 and (Down<0):-25

Case2:
Start -100
Series1: +150
Series2: 50

Here we should have:
Series1: (Up<0):+100 and (Up>0):+50
Series2: (Up>0):+50

Do you know of any easy way to adapt the formulas from peltiertech to this multiple series example?

Great thanks for any help.

Cheers
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Are the multiple series stacked? Shouldn't be too tricky, as long as the data in each stack has the same sign. Otherwise a single stack will try to increase and decrease, and it is not intuitive trying to display and to read this kind of data.
 

M1985

New Member
Joined
Jan 15, 2014
Messages
5
Are the multiple series stacked? Shouldn't be too tricky, as long as the data in each stack has the same sign. Otherwise a single stack will try to increase and decrease, and it is not intuitive trying to display and to read this kind of data.

Yes they are stacked, I agree with you shouldnt mix positive values with negative since it would confuse the viewer...
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
For a waterfall without stacks, you need a running subtotal for each category. For one with stacks, you need one for each floating bar, which means to the subtotal at the beginning of the category, you also need to include the amounts for the bars floating beneath the one you're currently plotting. I know I'm making it sound so complicated, but I've done it before. I just don't have easy access to the example.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top