turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 129K Members
- 1,095 Online
- 32K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Tables are fantastic but cumulative totals are a pain

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2016 06:03 PM - edited 09-12-2016 06:05 PM

Excel Tables have too many benefits to list here, if you've never used them then check out my article here

One frustration I do have though is that cumulative totals in a column are not straightforward.

In "normal" Excel to get a running total you just add the cell above to the cell on the current row, and that's it.

For a table, with it's structured references and headings this proves problematic.

Also, ideally you want a running total that works when you insert rows into the Table or add lines of data to the bottom of it.

So here are 2 options, and both make use of the INDEX function.

INDEX has several uses and one of them is to make a dynamic range.

This is a very odd concept when you first see it written

e.g. =SUM(A1:A10) can be re-written with an INDEX formula as

= SUM( INDEX(A:A , 1)** :** INDEX(A:A , 10) )

Essentially it's saying SUM everything between the 1st cell of A:A and the 10th cell of A:A

Stick with me, like I say, I know it's unfamiliar and a little weird.

Let's apply this to a Table that has weekly units sold for January and February

To keep things simple I've set it so that we've sold 1 unit a week.

So the formula we put in the Cumulative Sum column is

=SUM( INDEX( [Units Sold] , 1) : [@[Units Sold]])

So it adds up the values from row 1 of the Units Sold column as far as each row (that's where the @ [Units Sold] comes in)

I'll agree it's not a very attractive formula but it works and I wish there was a simpler way.

Importantly it works if you insert rows or add more data to the bottom of the Table. Brilliant!

**Option 2 : A little more advanced**

If you've stuck with me this far then it's worth reading this extra bit...

The SUM approach has a drawback.

If you filter the table for February you still get the YTD cumulative rather than just the February Cumulative

An alternative is the little known AGGREGATE function (you could also use SUBTOTAL).

=AGGREGATE( 9, 7, INDEX( [Units Sold] , 1) : [@[Units Sold]] ) )

The 9 means SUM

The 7 means ignore Hidden Rows AND Errors

So when the data is filtered the rows are hidden, and the Aggregate works nicely. Even if one of your values was an Error e.g. #VALUE or DIV/0 the cumulative would ignore it and the total still works.

This ignoring of elements can come in really useful, but just be careful that you really do want to do this! Spotting errors are normally a good thing.

Any suggestions of alternative approaches would be appreciated.

The file is located here if you'd like to download it

Link to Excel file on DOCS.com

Hope you find this useful

Wyn

AMAZING EXCEL SOLUTIONS

www.accessanalytic.com.au

Labels:

2 Replies

Highlighted
##
##### Re: Tables are fantastic but cumulative totals are a pain

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-14-2016 04:59 PM - edited 09-14-2016 05:03 PM

Nice write-up, but it's missing one essential thing:

Readers, be aware that for large data sets, this technique can slow down Excel performance when the sheet is recalculated. In every row, the formula will sum from the first table data row to the current data row. If you only have a few hundred rows, that's still fast. But if the data set goes into the hundreds of thousands of rows, you will notice a performance hit. In each row, Excel has to calculate an ever growing range of cells.

But in each row it also adds numbers that have already been added. This is unnecessary repetition of calculation and very inefficient.

In a table with 500,000 rows there are 499,990 formulas. In row 3 the formula processes 2 cells, in row 4 the formula processes 3 cells, [...], in row 499,999 it processes 499,998 cells, and in row 500,000 the formula processes 499,999 cells. That's a total of 125,000,249,999 to process, and that will take time.

A faster approach can be realised by adding just the previous row's total with the current row's sold units. That way, each of the 499,999 formulas calculates only two cells, 1 Million in total. Or, in a scenario with a debit and credit contributing to the balance, three cells, which would be 1,5 million cells to process.

Unfortunately, structured referencing does not have a handle for "the previous row", so we still need to revert to A1 notation to do this. Consider the following screenshot:

The first scenario calculates the running total with this formula starting in E3:

=SUM(E2,D3)

The Sum()function ignores text values, so in the first instance of the formula, E2 is ignored and D3 is added to the total.

The green table has the typical credit/debit pattern with the current balance. Here the formula in J3 is

=SUM(J2,H3,-I3)

Another way to write this formula would be

=N(J2)+H3-I3

The plus and minus operators don't tolerate text and will return an error if any of the cells contain text. The N() function converts a cell value to a number and will return 0 if it contains text, so the formula will not return an error in the first data row, despite using plus and minus operators and J2 referring to the column title, which is text.

One drawback with this solution is that when a row is inserted in the middle of the table, the formula in the inserted row will have a wrong reference. But Excel picks this up and sets the green warning triangle, which when clicked informs that the formula is inconsistent. A correct formula can quickly be restored by clicking the warning drop-down and selecting "Restore to calculated column formula".

In many situations that approach is faster than waiting for over a billion cells to be processed, but it can be argued that it's easy to miss the warning.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-14-2016 05:34 PM

Fair point @Ingeborg. An alternative for bigger data sets that can handle inserted / deleted rows would be this: I selected cell A2 and then defined the name "RowAbove" as = N(A1) Then my cumulative formula in the Table can be set as RowAbove + [@Units Sold]

Related Conversations

Don't count empty cells in pivot table

Anwesh Gangula
in
Charts and Visualizing Data
on
10-07-2017
565
Views

0 Likes

1 Replies

Cannot set default Pivot Formatting

Datafiltr Team
in
Excel
on
11-22-2017
358
Views

0 Likes

3 Replies

Pivot Table new feature "Expand to month/week"

Mehdi HAMMADI
in
BI and Data Analysis
on
02-15-2018
135
Views

0 Likes

7 Replies

Turn Off Calculated Columns

Benjamin Wooler
in
Formulas and Functions
on
11-26-2017
110
Views

0 Likes

6 Replies

Pivot tables with Form Answers and Changing Spreadsheets for Form Answers

Candice Washington
in
Microsoft Forms
on
12-20-2017
121
Views

0 Likes

0 Replies

Pivot Tables - Show Details in excel 2013

Simon Bryant
in
Excel
on
02-20-2018
45
Views

0 Likes

0 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft