Help - Complicated Waterfalls

I've come across waterfall charts (and read the various postings on this
forum, and the links to sites that tell you how to make them in excel)

So far so good.

The charts are great for clearly showing a single change (either a plus or a
minus), and cope with occasions where plusses and minuses are shown on the
same column (the next column starts in the right place).

What they don't show in those circumstances is the aggregate value (i.e.
plus 3000, minus 2000, aggregate is 1000 (3000-2000!), so would ideally show
as 1000 greater than the invisible.

Hope this makes sense.

Any ideas on how this can easily be achieved? (Without VBA as I'd like it
to keep up with changing data)

Ideally, I'd then simply show the aggregate section as a waterfall column,
but the plusses and minuses sticking out like stock market high and low
markers (whiskers).

Am I going too far, or is there any reasonably easily mantainable way of
building this in excel?

Many thanks in advance for any help you can provide.

Cheers,

Tom.

Tom -

Post back if you need more that this:

http://peltiertech.com/Excel/Charts/Waterfall.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:[email protected]...
> I've come across waterfall charts (and read the various postings on this
> forum, and the links to sites that tell you how to make them in excel)
>
> So far so good.
>
> The charts are great for clearly showing a single change (either a plus or
> a
> minus), and cope with occasions where plusses and minuses are shown on the
> same column (the next column starts in the right place).
>
> What they don't show in those circumstances is the aggregate value (i.e.
> plus 3000, minus 2000, aggregate is 1000 (3000-2000!), so would ideally
> show
> as 1000 greater than the invisible.
>
> Hope this makes sense.
>
> Any ideas on how this can easily be achieved? (Without VBA as I'd like it
> to keep up with changing data)
>
> Ideally, I'd then simply show the aggregate section as a waterfall column,
> but the plusses and minuses sticking out like stock market high and low
> markers (whiskers).
>
> Am I going too far, or is there any reasonably easily mantainable way of
> building this in excel?
>
> Many thanks in advance for any help you can provide.
>
> Cheers,
>
> Tom.

Yes please - your website is the one I used to get my head round waterfall
charts - it's very useful - esp the guidelines on how to lay out the data,
the formulae, etc.

To use the example on your site, your data table has columns:
Invisible Final Minus Plus Initial

If only Plus or Minus is completed for any given category, then the change
in that category is easy to see, and the next category will start level with
the endpoint of the previous one.

If, however there are both plus and minus values against the same category,
there is of course a more complex picture to portray. What I'd hoped to
chart was:
E.g. start point 10000.
Plus 5000 and minus 3000 in the first column.
So I'd want a column from 10000 to 12000 (the aggregate value
(+5000-3000=2000)). But as well as that, I would want to show what that was
made up from (i.e. represent the +5000 and the -3000) I guess the best way
to go that would be whiskers (like the stock movement charts) to show the
maximum defiation in any direction in that column.

Am I making any sense?

Many thanks,

Tom.

"Jon Peltier" wrote:

> Tom -
>
> Post back if you need more that this:
>
> http://peltiertech.com/Excel/Charts/Waterfall.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> news:[email protected]...
> > I've come across waterfall charts (and read the various postings on this
> > forum, and the links to sites that tell you how to make them in excel)
> >
> > So far so good.
> >
> > The charts are great for clearly showing a single change (either a plus or
> > a
> > minus), and cope with occasions where plusses and minuses are shown on the
> > same column (the next column starts in the right place).
> >
> > What they don't show in those circumstances is the aggregate value (i.e.
> > plus 3000, minus 2000, aggregate is 1000 (3000-2000!), so would ideally
> > show
> > as 1000 greater than the invisible.
> >
> > Hope this makes sense.
> >
> > Any ideas on how this can easily be achieved? (Without VBA as I'd like it
> > to keep up with changing data)
> >
> > Ideally, I'd then simply show the aggregate section as a waterfall column,
> > but the plusses and minuses sticking out like stock market high and low
> > markers (whiskers).
> >
> > Am I going too far, or is there any reasonably easily mantainable way of
> > building this in excel?
> >
> > Many thanks in advance for any help you can provide.
> >
> > Cheers,
> >
> > Tom.
>
>
>

I can imagine two options:



1. Construct the chart with two columns per category, one positive, one
negative. No need for the error bars.



2. Construct the chart with one column per category, which shows the net
positive/negative change. Then add positive error bars to show the
difference between the maximum for the day and the top of the column, and
negative error bars to show the difference between the minimum for the day
and the bottom of the column.



I have a page on error bars, if you need a refresher:



http://peltiertech.com/Excel/ChartsHowTo/ErrorBars.html



- Jon

-------

Jon Peltier, Microsoft Excel MVP

Peltier Technical Services

Tutorials and Custom Solutions

http://PeltierTech.com/

_______


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:[email protected]...
> Yes please - your website is the one I used to get my head round waterfall
> charts - it's very useful - esp the guidelines on how to lay out the data,
> the formulae, etc.
>
> To use the example on your site, your data table has columns:
> Invisible Final Minus Plus Initial
>
> If only Plus or Minus is completed for any given category, then the change
> in that category is easy to see, and the next category will start level
> with
> the endpoint of the previous one.
>
> If, however there are both plus and minus values against the same
> category,
> there is of course a more complex picture to portray. What I'd hoped to
> chart was:
> E.g. start point 10000.
> Plus 5000 and minus 3000 in the first column.
> So I'd want a column from 10000 to 12000 (the aggregate value
> (+5000-3000=2000)). But as well as that, I would want to show what that
> was
> made up from (i.e. represent the +5000 and the -3000) I guess the best
> way
> to go that would be whiskers (like the stock movement charts) to show the
> maximum defiation in any direction in that column.
>
> Am I making any sense?
>
> Many thanks,
>
> Tom.
>
> "Jon Peltier" wrote:
>
>> Tom -
>>
>> Post back if you need more that this:
>>
>> http://peltiertech.com/Excel/Charts/Waterfall.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>>
>> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
>> news:[email protected]...
>> > I've come across waterfall charts (and read the various postings on
>> > this
>> > forum, and the links to sites that tell you how to make them in excel)
>> >
>> > So far so good.
>> >
>> > The charts are great for clearly showing a single change (either a plus
>> > or
>> > a
>> > minus), and cope with occasions where plusses and minuses are shown on
>> > the
>> > same column (the next column starts in the right place).
>> >
>> > What they don't show in those circumstances is the aggregate value
>> > (i.e.
>> > plus 3000, minus 2000, aggregate is 1000 (3000-2000!), so would ideally
>> > show
>> > as 1000 greater than the invisible.
>> >
>> > Hope this makes sense.
>> >
>> > Any ideas on how this can easily be achieved? (Without VBA as I'd like
>> > it
>> > to keep up with changing data)
>> >
>> > Ideally, I'd then simply show the aggregate section as a waterfall
>> > column,
>> > but the plusses and minuses sticking out like stock market high and low
>> > markers (whiskers).
>> >
>> > Am I going too far, or is there any reasonably easily mantainable way
>> > of
>> > building this in excel?
>> >
>> > Many thanks in advance for any help you can provide.
>> >
>> > Cheers,
>> >
>> > Tom.
>>
>>
>>

Option 2 looks like the way to go, and your page on error bars should be just
the business.

Many thanks,

Tom.

"Jon Peltier" wrote:

> I can imagine two options:
>
>
>
> 1. Construct the chart with two columns per category, one positive, one
> negative. No need for the error bars.
>
>
>
> 2. Construct the chart with one column per category, which shows the net
> positive/negative change. Then add positive error bars to show the
> difference between the maximum for the day and the top of the column, and
> negative error bars to show the difference between the minimum for the day
> and the bottom of the column.
>
>
>
> I have a page on error bars, if you need a refresher:
>
>
>
> http://peltiertech.com/Excel/ChartsHowTo/ErrorBars.html
>
>
>
> - Jon
>
> -------
>
> Jon Peltier, Microsoft Excel MVP
>
> Peltier Technical Services
>
> Tutorials and Custom Solutions
>
> http://PeltierTech.com/
>
> _______
>
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> news:[email protected]...
> > Yes please - your website is the one I used to get my head round waterfall
> > charts - it's very useful - esp the guidelines on how to lay out the data,
> > the formulae, etc.
> >
> > To use the example on your site, your data table has columns:
> > Invisible Final Minus Plus Initial
> >
> > If only Plus or Minus is completed for any given category, then the change
> > in that category is easy to see, and the next category will start level
> > with
> > the endpoint of the previous one.
> >
> > If, however there are both plus and minus values against the same
> > category,
> > there is of course a more complex picture to portray. What I'd hoped to
> > chart was:
> > E.g. start point 10000.
> > Plus 5000 and minus 3000 in the first column.
> > So I'd want a column from 10000 to 12000 (the aggregate value
> > (+5000-3000=2000)). But as well as that, I would want to show what that
> > was
> > made up from (i.e. represent the +5000 and the -3000) I guess the best
> > way
> > to go that would be whiskers (like the stock movement charts) to show the
> > maximum defiation in any direction in that column.
> >
> > Am I making any sense?
> >
> > Many thanks,
> >
> > Tom.
> >
> > "Jon Peltier" wrote:
> >
> >> Tom -
> >>
> >> Post back if you need more that this:
> >>
> >> http://peltiertech.com/Excel/Charts/Waterfall.html
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com/
> >> _______
> >>
> >>
> >> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> >> news:[email protected]...
> >> > I've come across waterfall charts (and read the various postings on
> >> > this
> >> > forum, and the links to sites that tell you how to make them in excel)
> >> >
> >> > So far so good.
> >> >
> >> > The charts are great for clearly showing a single change (either a plus
> >> > or
> >> > a
> >> > minus), and cope with occasions where plusses and minuses are shown on
> >> > the
> >> > same column (the next column starts in the right place).
> >> >
> >> > What they don't show in those circumstances is the aggregate value
> >> > (i.e.
> >> > plus 3000, minus 2000, aggregate is 1000 (3000-2000!), so would ideally
> >> > show
> >> > as 1000 greater than the invisible.
> >> >
> >> > Hope this makes sense.
> >> >
> >> > Any ideas on how this can easily be achieved? (Without VBA as I'd like
> >> > it
> >> > to keep up with changing data)
> >> >
> >> > Ideally, I'd then simply show the aggregate section as a waterfall
> >> > column,
> >> > but the plusses and minuses sticking out like stock market high and low
> >> > markers (whiskers).
> >> >
> >> > Am I going too far, or is there any reasonably easily mantainable way
> >> > of
> >> > building this in excel?
> >> >
> >> > Many thanks in advance for any help you can provide.
> >> >
> >> > Cheers,
> >> >
> >> > Tom.
> >>
> >>
> >>
>
>
>