Contextures

Contextures News 2020-10-20

Excel Running Total Trouble

October 20, 2020

Spill functions, running total problems, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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.

Spill Formulas

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:

=OFFSET(C4,I17,0,3,3)

range reference spills into adjacent cells

Another example combines the new function, SEQUENCE, with the old function, TODAY, to create a 4 row, 7 column calendar:

=SEQUENCE(4,7,TODAY())

sequence function two examples

To see more of Dermot's examples, and to get the sample file, go to the Excel Spill Functions page on my Contextures site.

Running Total Problem

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)

incorrect running total in named Excel table

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.

Excel Articles

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. (Level - Adv)

Also see: My Excel Products || Previous Issues

Ready for Winter

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?

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: October 19, 2020 2:55 PM