Excel 97 or Powerpoint 97 waterfall chart 2

[JOZ]

To the Excel 97 and Powerpoint 97 gods and goddesses:

Do you know of an easy way including a shareware download, that I can use to create a waterfall chart in Excel 97 or Powerpoint 97? A waterfall chart is a bar chart that looks like this:

| X X
| X X
| X X
| X X
| X X
|___X__________________ X________

A B C D E

"E" is the sum of A through D. An example might be revenues, where you can get some revenues by doing A, some by doing B, etc. until the sum of A through D are enough to reach your goal of E. The y-axis is $$$ so A might be $50, B $25, C $55, and D $20 so E is $150. The "X"s above would be bars like in a bar chart with the size of the bar being relative to the size of the $ amount but except for A and E, the bottom of the bars do not touch the x-axis.

Sincerely,

A Mere Mortal

 

[JVFriederick]

This sounds like supply-demand curves.

Is it absolutely necessary that it be in Bar chart format?
If not, then a cumulative x-y chart would work easily.

 

[JOZ]

JVFriederick,

Thanks for your response.

The bars allow the viewer to see the relative differences in the dollar amounts. Also, if there is a gap between the sum of the pieces and the goal, the gap can be shown as a bar as well. In the example above, A through C could be the pieces and D could represent the gap between the A through C sum and the goal E. I'm not sure an X/Y chart would get to the relative differences and the gap issue.

 

[JVFriederick]

Send an Email to JVFriederick@Yahoo.com and I'll send an example file. I used High-Low-Close chart, and I think it's pretty close to what you wanted.

 

[DaleWatson123321]

Hi JOZ,

Here's another option. I've just set it up in Excel and it works perfectly. It simply sets the "row height" according to the dollar value.

Using your example, I simply replaced your "Xs" with the dollar values you supplied. I then colored the cells containing the numbers and gave them a black border. I also colored the text with the same interior color (to hide the numbers of course). But you could alternatively display the numbers (as some charts do). As you probably know, Excel allows you to center numbers horizontally and vertically, or place them at the top of a cell.

The following VB code quickly sets the row height according to the value in each cell of your Total column (E). NOTE: You need to assign the range name "top" to the cell immediately above the uppermost cell in your Total column.


Sub Set_Height_Start()
Application.ScreenUpdating = False
Application.Goto Reference:="top"
Set_Heights
Application.ScreenUpdating = True
End Sub

Sub Set_Heights()
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then Exit Sub
Set_Row
Set_Heights
End Sub

Sub Set_Row()
num = ActiveCell.Value
Selection.RowHeight = num
End Sub


If the above code doesn't make complete sense, please email me at dwatson@bsi.gov.mb.ca and I will send you the file.

Regards, ...Dale Watson

 

[DaleWatson123321]

P.S. I forgot to include the Declaration:
Dim num as Double

...Dale Watson