How to show multiple lines in a waterfall chart?

[LRATOZ]

Hi,
I want to create a waterfall chart that shows the accumulative value of rainfall for each year.
So basically the line can only go up.
The X-axis shows the days from 0 to 366
The Y-axis shows the rainfall value in mm (0 to 1500)
I am able to do that for one year but if I want to add a second year than Excel doesn't allow me to that. I am using Excel 2016 on a Windows 10 machine.
So basically I want to see three lines projected on top of each other for following data:


='Obs data'!$B$53:$B$418
='Obs data'!$B$419:$B$784
='Obs data'!$B$785:$B$1150

At this stage I can only show the first line.
I would have added a screenshot of my graph but unfortunately this can't be done.
Can I create a waterfall chart from scratch without using the included templates?
Many thanks in advance!

 

[Gerald Higgins]

Hi, why do you want a waterfall chart for this ?

Wouldn't a simple line chart be better ?
In which case the three ranges you have posted would be the source data for three separate chart data series ?

 

[LRATOZ]

Hi Gerald,

A line chart would go up and down. I want to see accumulative values.
So, every time it rains the line will go up. When there's no rain the line will stagnate.
A line chart would go back to zero when there's no rain.
I hope this explains it a bit better.
Thanks for asking.
Cheers.

 

[Gerald Higgins]

A line chart would only go up and down if the data that drives it goes up and down.
If your data only ever goes up, the line chart would only ever go up.

And besides, waterfall charts are specifically designed to go up and down, at least according to wikipedia . . .
https://en.wikipedia.org/wiki/Waterfall_chart

You say you are using Excel 2016, I don't know if that includes its own Waterfall Chart type, the version of Excel that I am using is older and does not have Waterfall as a standard type.

I specifically suggested a line chart because IF you want to show data for several years side by side, then the lines might cross each other.
That is easy to show in a line chart, but could get very complicated in a true waterfall chart.

I think what you need to do is set up your data so that you have formulas that accumulate the data, like this . . .

DATE..........DAILY RAINFALL......CUMULATIVE RAINFALL
Jan 1..........5..........................5
Jan 2..........3..........................8
Jan 3..........0..........................8
Jan 4..........4.........................12

and so on.
Then make the data for the CUMULATIVE rainfall drive your chart series, not your DAILY rainfall

The formula for the accumulation is simple - for example in the above example data for Jan 2 it could be
=C2+B3

 

[LRATOZ]

Excellent idea Gerald!
I took this on board.
Yes, I think you are correct: line charts will be around forever but all the other gimmicks might or will disappear in new versions of software.
I like your approach to keep it simple.
Many thanks taking the time to explain this to me!