Excel Challenge: What Does this Formula Do?

The Scenario

We're taking over a workbook that someone else was using and there's an important but complicated formula in it. We need to know 2 things:

• What does it do?
• How can it be modified for a different purpose?

Here's the formula (the video is later in this post):

The Strategy

Rather than look at the long, nested formula, in the video, I invite you to play with the inputs and see how the output changes.

This is deliberate instruction because sometimes a formula is truly too overwhelming to comprehend, but that doesn't make the need go away. Sometimes we can figure out a formula's purpose by playing with it and seeing what it does.

The formula of question is in cell D5 (the pink cell). It calculates out to 2 when the inputs are January, Sales Rep: Gerri, Level: 25

In the following image, the result is 6 when the inputs are February, Sales Rep: Jean, Level: 25

What I want to highlight with this video is a difference between knowing that something works vs. knowing precisely how it works. This is important because real-world needs don't wait for us to be ready. Sometimes we need someone else to write a formula for us or we copy VBA code off of a website. We don't fully understand all the details. But we can test scenarios to see if it's working, and figure out enough to make minor modifications.

On multiple occasions I've worked with clients ... 2 or more hours spent developing a single formula. They don't know how to write the formula, but they know what the result should be, given certain inputs.

That's what we want to get at today. If you can read the formula, great. If you can't read the formula, can you play with it and figure it out?

Here's the video challenge and the solutions

Breaking the formula down (for those who are interested)

• COUNTIFS counts the number of entries in range based on 1 or more criteria. Our criteria are: Month, Level and Rep
• INDIRECT tells Excel to treat the parts of the formula as if they were a normal reference. An example might explain better.

=A3 refers to cell A3

=INDIRECT ("A"&3) also refers to cell A3.

So, why not just type 'A3'?

INDIRECT is helpful when the A and/or the 3 need to be calculated and that's what's happening in this challenge. We're building a reference to the Rep column in either the Jan or Feb tables.

If cell B5 contains 'Jan' then the reference will be: Jan[Rep].

That tells the formula to perform the COUNTIFS in the Rep column of the January table. By using the dropdown list and INDIRECT, the reference can easily point to the February table, and then the March table when it's available.

• Overview! refers to the Overview sheet. The exclamation point always follows the name of a worksheet.
• [REP] refers to the REP column in a table. The square brackets are what tells us we're looking at a table, not just plain cells. [Count] is the same thing, it refers to the Count column in the table.

Putting everything together. This formula allows the user to select the Rep, Month and Count level and the formula returns the number of entries for the Rep in the selected month that are greater than or equal to the Level.

Conclusion

There's a lot going on here. This challenge is about being savvy. If you can't read the formula, play with it and see what it does.

Please, ask any questions that you might have.

See you next week with the next Excel Weekly Challenge!