Excel Running Total Trouble
October 20, 2020
Thank you for reading the news, and you'll get the next email in two weeks, on November 3rd.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Thanks to Dermot Balson, who sent me his Excel workbook with examples for all the new functions (dynamic arrays) in Excel for Office 365.
The examples also show how even the old Excel functions, such as OFFSET, will spill automatically, if your version of Excel supports spill functions. This formula returns 3 rows and 3 columns of data:
Another example combines the new function, SEQUENCE, with the old function, TODAY, to create a 4 row, 7 column calendar:
To see more of Dermot's examples, and to get the sample file, go to the Excel Spill Functions page on my Contextures site.
For a list on a worksheet, this formula works well to create a running total, based on the amounts in column C: =SUM(C$2:C2)
However, if you use that formula in a named Excel table, things can go horribly wrong. For example, cell D6 had this formula: = SUM(C$2:C6)
When a new row started, the D6 formula automatically changed to this: =SUM(C$2:C7)
To avoid that problem, use a heading cell as the locked starting point (C$1), and a structured table reference to the Amt cell in the current row: =SUM(C$1:[@Amt])
You can read more about the running total problem, and download the workbook, on the Excel Sum Functions page of my Contextures site.
Here are a couple of Excel-related articles that you might find useful or interesting.
Spreadsheet Day: If you missed the online History of Excel event on Oct. 14th, at the Toronto Excel User Group, watch the event recording, in my Spreadsheet Day Roundup. Rob Collie said, "This is probably the best online event I've ever been part of." (Level - All)
Excel Tips: Jon Acampora shares his tips for naming Excel files, to keep things organizced (Level - All)
Spreadsheet Risks: Excel has been in the news again, blamed for the contact-tracing fiasco. But David Lyford-Smith, from ICAEW, explains that "the problem isn’t spreadsheets, but people". (Level - All)
Formulas: Kevin Lehrbass tackled Robert Gascon’s shortest formula challenge, and it's for experts only! If you download Robert's Excel file, go to cell A1, to see the data. Update: Robert posted his solutions. (Level - Adv)
Halloween is just around the corner, so it's time to prepare for winter, here in Canada. We got our flu shots last week, and a new batch of masks, from a small company in Montreal. My other winter prep purchase is a weighted blanket - the Mayo Clinic says it might help with better sleep, and reducing stress. And who doesn't need that?
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20201020ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: January 11, 2021 9:50 AM