Posted by Leanne de Guerrero on January 11, 2002 11:54 AM
Does anyone know how to use Excel to create Waterfall Charts?
I have tried to use the 'Stock' chart but still have to manually insert arrows between the columns and any value labels
Posted by Loren on January 14, 2002 5:29 AM
I think it's easier than you made it. Cyan's chart requires a starting
value, plus each value added to it (built upon it). I put a dummy
starting value in A2, and the added values in A3 to whatever. In B2 to
H2 I put the labels Start, Finish, Min, Max, Blank, Neg, and Pos. These
are the Min and Max of each column, the required amount of blank between
X=0 and the column (if it doesn't cross X=0), and the negative and
positive sections of the column.
In B3 I put the formula =A2
In C3 I put the formula =B3+A3
In D3 I put the formula =MIN(B3:C3)
In E3 I put the formula =MAX(B3:C3)
In F3 I put the formula =IF(D3>=0,MAX(D3,0),MIN(0,E3))
In G3 I put the formula =MIN(0,D3)-MIN(E3,0)
In H3 I put the formula =IF(E3>0,E3-MAX(F3:G3),0)
In B4 I put the formula =C3
The formulas in B4 and in C3:H3 are dragged down as far as there are
values in column A.
I plotted the values under Blank, Neg, and Pos in a stacked column
chart. The series Blank I formatted to be blank (no border, no fill),
to make the other columns float. The series Neg and Pos I formatted
with the same fill and border, so the column crossing X=0 would look
like a single column (except for the line at X=0 where the two meet).
To get the labels, I plotted another series on the chart, under Max. I
selected just this column, and from the Chart menu, I selected Chart
Type, and picked a line style. Now this gave me a series that lined up
with the top of each unblank column. I used Rob Bovey's XY Chart
Labeler (a free download from http://www.appspro.com) to apply the
additive values in column A (omit the starting value) to this added
series, then I formatted the line and markers of this new series to
None, so it disappeared (except for the labels).
For the connecting lines (which unfortunately don't look perfect), I set
up additional series, one series per connection. The first connector
had the first two values equal to the level where the first bar stopped
and the second started, all the rest blank. The second connector had
the first value blank, the second and third equal to the value where the
second bar stopped and the third started, etc. I added all these as
line style series, with no markers and a black line. When I did this
once before I used XY series for the connectors, and did elaborate
calculations to determine how to make the lines long enough to reach the
full widths of the columns they were connecting. The way I outline here
is much easier, but the lines connect the column midpoints, so you can
see the ends of the lines, which are really a pixel below the upper
column being connected. I hate pixels.
This approach seems to deal with positives and negatives pretty well,
but I'm sure someone can find conditions that will break it.