Advice on understanding/deconstructing complex Excel Formulas

[QMAN223]

Hello Community!

I wanted to ask for your advice on deconstructing complicated Excel formulas?

I tried searching for videos and advice on breaking down complex formulas so it becomes more clear what each piece of the puzzle does to generate the result. Very few videos provide such advice on deconstructing formulas.

I know of the Excel evaluate feature but that isn't a good solution for really long formulas as you loose track of what actually happens in the previous 10 methods before.

Please can you Excel experts share your thought process in deconstructing formulas so its more understandable?

Many thanks in advance!

 

[6StringJazzer]

When I have to analyze a formula I start from the innermost expressions and work my way out, replacing each inner expression with a word or two that explains what it is. There is not just one way to do this, and you can't really have an algorithm that will work with every formula. Here's an example of one I did for someone.

However, there are some types of formulas that take advantage of quirks in how to use the functions, and it is often not clear how they work. One example is

=LOOKUP(2,1/(A2:A10=D2),B2:B10)

Here is a link to a thread in another forum that explains it. With examples like this it may be possible to figure out what's going on if you understand how the LOOKUP function works, but often you just need someone to explain it.

 

[QMAN223]

When I have to analyze a formula I start from the innermost expressions and work my way out, replacing each inner expression with a word or two that explains what it is. There is not just one way to do this, and you can't really have an algorithm that will work with every formula. Here's an example of one I did for someone.

However, there are some types of formulas that take advantage of quirks in how to use the functions, and it is often not clear how they work. One example is

=LOOKUP(2,1/(A2:A10=D2),B2:B10)

Here is a link to a thread in another forum that explains it. With examples like this it may be possible to figure out what's going on if you understand how the LOOKUP function works, but often you just need someone to explain it.

Hi, thanks for your reply! That's an interesting thought process.

I was meddling around today at work with a formula that I had to write in PowerQuery and found it a little challenging figuring out what it was doing. I saw a video on YouTube (linked below if you're interested) that taught me to break the formula down into its individual bits so I can understand what each part of it does to arrive to its final result.

I followed the guidance and it was great! I was able to deconstruct the formula and solve the problem I was having the past few days.