troubleshooting Question

Ideas how to document complex Excel formulas?

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft OfficeMicrosoft Excel
26 Comments1 Solution42 ViewsLast Modified:
Hi. I just lost a couple of weeks because one of my formulas was not documented and I forgot a basic facet of its implementation.

For interest here is the formula:

=IF(EXACT([@INV],"Invoice"),LAMBDA(INVNO,LEDGER,NL,PAYEE,LET(many,ManyPaid(NL,RAs[[#All],[LOOKUP]]),IF(many,INDEX(RAs[[#All],[CrossRefs]],many,0),Allocator(INVNO,LEDGER,NL,PAYEE))))([@INVNO],[@LEDGER],[@NL],[@Payee]),"")

It works fine. Now. However somehow I had lost that initial IF(Exact which resulted in triplicated results when read by another formula (the one in my most recent questions).

As a result I have spent days, over a week, debugging the wrong formula.

Even the most simple documentation would have allowed me to spot where the problem really was.

However, these functions are not like writing code in an IDE where you can comment everywhere. So I see a need for both general "this is how this works" commenting as well as detailed "this does this" comments within a formula - and some help with counting where the brackets open and close. I spend ages doing this last trying to reverse engineer the formulas of both myself and others.

In days of yore I could read formulas and macros as if they were written in English, but no longer. They are just too convoluted especially when recursive.

I suppose a notepad with maybe a table would do it, but then every time a small change is made it is twice the time to update the docs, longer in fact.

Regards

Anthony





Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 26 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros