What Nerds can teach you about Mastering Excel Formulas

“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):

2x+2+3=7

Add the 2 and 3 (on the left side of the equation):

2x+5=7

Subtract 5 from both sides of the equation:

2x=7-5

Make the subtraction on the right side (please keep reading, there’s a point to this – I promise):

2x=2

And finally divide both sides of the equation by 2 to arrive to the result:

x=1

But for my friends, this was too boring. They couldn’t be bothered with the simple steps. They would go from:

2(x+1)+3=7

To:

X=1

In one step!!!!!

And more often than not, they would go from:

2(x+1)+3=7

To:

x=2

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?

Plenty.

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?

No?

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:

I am required to write a formula that will show (for each invoice) the amount of days left until it needs to be paid (assuming that invoices are supposed to be paid within 90 days) and if more than 90 days have passed then I should mark the invoice as “Late Payment”.

So, instead of charging ahead and immediately writing the formula in Excel, let’s break down the problem first.

We need to:

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:

Note: The Edate() function turns a date-value into a number which is the number of days since 1/1/1900. And so I use the Edate() function to convert both the current date (today()) and the date on which the invoice was issued into a [days since 1/1/1900] representation. And then all I have to do is subtract the two resulting numbers to get the number of days that have passed since the invoice was issued.

Now, let’s calculate how many days till payment (in another column):

Then use the ‘if’ function to display the required result:

Note: I use the if() function to check if less than 90 days have passed since the invoice was issued. If less than 90 days passed, the if() function returns the days left until payment (Cell E2). If more than 90 days have passed, the if() function returns the value “Late Payment”.

Finally, let’s combine all the separate formulas into one formula:

Summary

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.

 

P.S.

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.