Fixing Broken Functions in Spreadsheets

Continuing our series on the "Top 10 Dysfunctions of #Spreadsheets" -- consider # 3 on our list: #BrokenFunctions. Such a sneaky one! What to do?! 👇

Plan that it may happen to your spreadsheet. No way around it, that we know of.

So you’ll want to build in a process to carefully *trace through formulas*. It can happen with something as simple as adding new row into a sheet, without accounting for which formulas might apply to adjacent rows. Hidden in plain sight.

#Excel will do the best that it can to warn you about a broken formula. But sometimes the formula generates inaccurate results simply from offset / whacked inputs, not from a broken formula per se. ⛔️

------------

Two ideas for you:

1. In #spreadsheets, consider trying to work more often with tables, and #tableFormulas. That’ll help a lot!

I have a friend and colleague Gary who is an Excel guru, and you should see the things that he can do with a table or array formula. Yowza! So there is hope …

2. In a #system, your formulas will look very familiar, and hopefully even simpler to implement and maintain. If you get your data model correct, then your system will be built to scale. 📈

Get that data model right, and all else takes care of itself!

Thank you.