“When I was younger, just a bad little kid my mama noticed funny things I did…”
— Orin the Dentist, Little Shop of Horrors.
When I was young and just a nerdy little kid, I was really good at solving mathematical equations. So good, that my friends’ parents took notice and hired me to tutor their kids (my friends).
And because they offered a pile of money, I more than happily accepted the challenge.
It was then that I noticed a funny thing. I was the best equation solver in class! No kidding – A+ in every test.
And yet, when I solved an equation I did it one small step at a time.
So if you take this equation for instance:
2(x+1)+3 = 7
I would expand the equation (get rid of the parenthesis):
Add the 2 and 3 (on the left side of the equation):
Subtract 5 from both sides of the equation:
Make the subtraction on the right side (please keep reading, there’s a point to this – I promise):
And finally divide both sides of the equation by 2 to arrive to the result:
But for my friends, this was too boring. They couldn’t be bothered with the simple steps. They would go from:
In one step!!!!!
And more often than not, they would go from:
This would earn them an F on the exam. This, in turn, provided me with a generous and continuous income stream.
You see, all my friends knew how to solve math equations. But because they were in a hurry to solve the equation, they skipped a lot of steps, and since it is difficult to solve an equation when skipping steps, they made mistakes.
What does that have to do with Excel Formulas?
Whenever we create a compound formula (a formula that uses more than one function), we are actually solving more than one problem. And if we rush things and try to write the entire formula in one go, there’s a good chance we’ll make a mistake or two.
Here’s where another reframing technique comes into play – Breaking down the problem.
Breaking down the problem allows us to solve every part of the problem separately and then join the small solutions into one big solution (or in our case one big formula).
Is that clear?
Fair enough, let’s check out this example…
Breaking Down the Problem – Example
Let’s say I have a list of unpaid invoices. Along with each invoice I have the date on which it was issued – as shown below:
So, instead of charging ahead and immediately writing the formula in Excel, let’s break down the problem first.
We need to:
- Calculate how many days have passed since each invoice was issued
- Calculate how many days until payment, which is: 90-[days passed since issued]
- If 90 days or less passed from the issue date then display [days until payment] otherwise display “Late payment”
Ok, that wasn’t so hard; now let’s enter this into Excel.
First, an formula to calculate how many days passed since invoice was issued:
Now, let’s calculate how many days till payment (in another column):
Finally, let’s combine all the separate formulas into one formula:
When faced with a situation that requires a compound formula, you should start by breaking down the problem into the smallest possible parts, then solve each part separately and only when all the parts are working, combine the little formulas into one.
And, as usual, don’t forget to check out our PDF to Excel Converter. It can help you free up a lot of time. Time you can use to get a lot more done at work or … to do something nice for yourself. Go walk in the park, watch a movie, talk to a friend – you deserve that.