Waterfall Chart - 3 items

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Waterfall Chart - 3 items

  1. #1
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    INDIA
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Waterfall Chart - 3 items

     
    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.
    Regards,
    Pedie
    MS OFFICE 2016/EXCEL 2016: Window7: 64BIT
    Visit YouTube.com/VBAa2z


  2. #2
    Board Regular
    Join Date
    Nov 2006
    Posts
    414
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

    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
    ABCDEFG
    1How to make a waterfall chart
    2
    3
    4
    5Three column method works if all the changes (Dark values) are positive
    6clearvisible
    7start0100
    8a10050
    9b15030
    10c18020
    11d20015
    12e 21510
    13f2255
    14end0230
    15
    16
    17
    18If there are negative values - need an extra column.
    19clearabsvisible
    20start0100100
    21a1003030
    22b1301515
    23c1451010
    24d1505-5
    25e 14010-10
    26f12020-20
    27end0120120

    Sheet1



    Worksheet Formulas
    CellFormula
    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)

    -R

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Posts
    414
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

    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.
    -R

  4. #4
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    INDIA
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

    Thanks tybaltlives; 'm gonna pm you my email address. in the mean time trying the data you provided.
    Regards,
    Pedie
    MS OFFICE 2016/EXCEL 2016: Window7: 64BIT
    Visit YouTube.com/VBAa2z


  5. #5
    Board Regular
    Join Date
    Nov 2006
    Posts
    414
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

    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
    -R

  6. #6
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    INDIA
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

    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
    Regards,
    Pedie
    MS OFFICE 2016/EXCEL 2016: Window7: 64BIT
    Visit YouTube.com/VBAa2z


  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,400
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

    My tutorial on waterfall charts in Excel includes a compound waterfall example:
    Excel Waterfall Charts (Bridge Charts)
    This may have saved a little time.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  8. #8
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    INDIA
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Waterfall Chart - 3 items

      
    Jon Peltier, thanks alot for directing me to your site. I'm fan of your site.
    Finally got it working...
    Pedie
    Regards,
    Pedie
    MS OFFICE 2016/EXCEL 2016: Window7: 64BIT
    Visit YouTube.com/VBAa2z


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com