Waterfall Chart - 3 items

[pedie]

Hi everyone, 'm trying to create a waterfall chart with 3 items for each month.
I found relevant looking chart but need someone to help me out on how to set this up in excel.
Compound Waterfall



Thanks everyone in advance for helping.

[tybaltlives]

A waterfall chart is simply a stacked bar chart with a portion of the stacked bars formatted to be clear (you do this manually).
to create the waterfall effect.

There are two possibilities depending on if your data contains all positive or mixed positive and negative values. If both positive or negative values are present, you need three columns. One is the absolute value of the data column that contains the negative data.

Here is some data to show you how it works. Just plot the "start" column and the next two columns.

Excel 2010

	A	B	C	D	E	F	G
1	How to make a waterfall chart
2
3
4
5	Three column method works if all the changes (Dark values) are positive
6		clear	visible
7	start	0	100
8	a	100	50
9	b	150	30
10	c	180	20
11	d	200	15
12	e	215	10
13	f	225	5
14	end	0	230
15
16
17
18	If there are negative values - need an extra column.
19		clear	abs	visible
20	start	0	100	100
21	a	100	30	30
22	b	130	15	15
23	c	145	10	10
24	d	150	5	-5
25	e	140	10	-10
26	f	120	20	-20
27	end	0	120	120

Sheet1

Worksheet Formulas Cell Formula B8 =SUM(C$7:C7) B9 =SUM(C$7:C8) B10 =SUM(C$7:C9) B11 =SUM(C$7:C10) B12 =SUM(C$7:C11) B13 =SUM(C$7:C12) C14 =SUM(C7:C13) C20 =ABS(D20) B21 =IF(D21>=0,SUM(D$20:D20),SUM(D$20:D20)+D21) C21 =ABS(D21) B22 =IF(D22>=0,SUM(D$20:D21),SUM(D$20:D21)+D22) C22 =ABS(D22) B23 =IF(D23>=0,SUM(D$20:D22),SUM(D$20:D22)+D23) C23 =ABS(D23) B24 =IF(D24>=0,SUM(D$20:D23),SUM(D$20:D23)+D24) C24 =ABS(D24) B25 =IF(D25>=0,SUM(D$20:D24),SUM(D$20:D24)+D25) C25 =ABS(D25) B26 =IF(D26>=0,SUM(D$20:D25),SUM(D$20:D25)+D26) C26 =ABS(D26) C27 =ABS(D27) D27 =SUM(D20:D26)

[tybaltlives]

PS - I can send you the excel file that demonstrates the technique if you PM me your email address. Mine is only two stacked sets of data but it wouldn't be any different for three - you just need to leave one set of data as clear.

[pedie]

Thanks tybaltlives; 'm gonna pm you my email address. in the mean time trying the data you provided.

[tybaltlives]

OK. I can send you the file once I get your email. I think I worked out a way to do it with two visible data sets and one invisible one. That will be in the file I send you along with the existing waterfall charts.

-R

[pedie]

I have sent the updated chart using your logic...
now I want my chart to calculate the correct totals.
It is almost whatt I want now

[Jon Peltier]

My tutorial on waterfall charts in Excel includes a compound waterfall example:
Excel Waterfall Charts (Bridge Charts)
This may have saved a little time.

[pedie]

Jon Peltier, thanks alot for directing me to your site. I'm fan of your site.
Finally got it working...
Pedie