0

Sorry for asking a question more for a StupidUser than a Superuser but I'm at my wit's end.

I have one column of dates (x-axis values), and one of transactions (y-axis values).

Date        Amount
3/5/2017    665.16
3/6/2017    -100.00
3/6/2017    -35.66
3/6/2017    -9.24
3/6/2017    -26.32
3/6/2017    -56.70
3/7/2017    -7.72
3/7/2017    -5.78
3/8/2017    -11.00
3/8/2017    -129.00
...

I want to chart this data so that I can see how the total changes over time. So for example at 3/5/2017 the data point is at 665.16, and then at 3/6/2017 it's 437.24 (665 -100 -35.66 -9.24 -26.32 -56.70)

I haven't even been able to get the raw numbers in the chart, nevermind the running total. I tried by highlighting the two columns, selecting "Insert Waterfall Chart", but I haven't been able to get the data to actually get into the chart correctly.

I've also tried using a pivot chart, hoping its supposedly more-advanced features would let it intuit what I wanted the data to be, but no luck there either. I was able to get it to show the count of transactions on a given day, but not to sum them, even by changing "Count of Amount" in Values to "Sum of Amount".

I'm pretty sure it's excel 2016, but I couldn't even find the About entry in the menu so if you tell me that I can find out for sure.

2 Answers 2

2

When the data for the X axis is numeric (dates are numbers) then Excel will plot the data as a data series. To avoid that, remove the word "Date" from the column heading, so the cell above the dates is empty. Then Excel will put the dates as a timeline on the X axis of a column chart.

You have several duplicate dates for some days, so you could build a pivot chart with dates in the row and the amount in the values, then build a column chart.

But it seems that you don't really want to plot the differences, but you first want to calculate what the final value for that day should be. These calculations will not happen automatically in a chart. You will need to have the result of these calculations in the worksheet, then use these for the chart.

So, create a helper table. Start with the first date and get the starting balance for 5th March. Then enter the desired dates down the column. You can now use a formula to calculate the total balance for each day. Start in F3 with this formula and copy down.

=F2+SUMIFS(B:B,A:A,E3)

Then you can build a chart (remember to keep the cell above the dates blank)

enter image description here

If you want a waterfall chart with Excel 2016, then you could add a total row at the end where you sum all values above. Then insert a waterfall chart and mark the last data point as a total.

enter image description here

If you want to work out what version of Excel you are using, click File > Account.

-1

You need to have "one day - one value" scheme. To do this:

1) Remove duplicates:

enter image description here

2) Transpose column to row:

enter image description here

3) Use SUMMIF in cell below the Date value (referring to your original table) where Condition will be Date value above:

enter image description here

4) Now you are ready to plot a chart.

1
  • Why the transposing? That is absolutely not required to plot a chart. By the way, the downvote is not from me, but I can see how people would downvote.
    – teylyn
    Apr 25, 2018 at 2:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .