Everything you need to master Excel BI Challenge 1
The challenge
This is the first challenge in the Excel BI challenge series. We are asked to provide a formula to count the number of cells having only 2 words in range A2:A20.
The answer should be 8
Here is the link to the base Excel BI challenge file. You can view the Excel BI LinkedIn post for the challenge here.
Challenge walkthrough with Giles Male
Giles has recorded his attempt at this challenge live; no prep, no fluff, just opening the file and getting going. This means that you get to watch Giles' thought process and his journey, "warts and all".
You can watch Giles' walkthrough video on our Excel on the Road Channel.
Let's work through this challenge together
In this article, I am going to work through the core elements of the challenge. I recommend reading this article and then working through the video with the help of our supporting Excel file.
Please download the supporting Excel file. This file includes all of the formulas from Giles' walkthrough, complete with the full logic and workings with supporting guidance. It also covers alternative solutions including a simple, helper-column based version and a PowerQuery solution.
Before you get stuck into the video and supporting Excel file, let's step back and think about the logic behind this challenge.
The challenge: We need to determine how many cells contain two words. If a text string contains two words, it will contain exactly one space.
The first calculation will therefore need to determine which cells contain text with exactly one space. To do this we need to flag cells where the difference in text string length between the original text and the text with all blanks removed is equal to 1.
Once we have this list of cells containing 1 space we then need to count the number of these cells.
Ok, now that we have the logic in mind let's take a look at the two core elements of the logic flow:
Logic element 1 - Does the text string contain exactly one space?
Giles covers this part of the challenge in the first five minutes of his walkthough video.
You can find all of the details for this step in the Workings tab of the supporting Excel file.
Step 1 :
We need to find the length of the text in each cell:
=LEN(A2:A20)
This uses the LEN function to find the length of the text string in the cell. It also leverages the dynamic array engine to calculate this with the formula entered in just one cell, which then spills down the range.
Step 2:
Next, we need to find the length of the text in each cell if you remove the blanks:
=LEN(SUBSTITUTE(A2:A20, " ", ""))
This uses the SUBSTITUTE function to replace the blank (" ") with no blank (""). Then LEN is used as it was before.
Step 3:
Finally, we need to find the difference between the text string lengths from step 1 and step 2:
=LEN(A2:A20) - LEN(SUBSTITUTE(A2:A20, " ", ""))
That is the first logical element of the challenge complete.
So now all we need to do is count the number of cells returning a 1.
Simple...
Or is it?
Logic element 2 - Count the number of cells where the result is 1
This seems like it really should be very simple. We all know how to count some cells where the value is 1 after all!
Well, it is super simple if you use helper-columns to solve the challenge. I have shown this in the "Simple Solution" tab in the supporting Excel file.
The difficulty comes when you try to solve the whole thing in one cell. This is what you will see Giles work through in the rest of his walkthrough video.
Giles takes us on a journey through various possible solutions including COUNTIFS, SCAN, REDUCE, FILTER and LET.
I highly recommend that you work through the video alongside the Workings tab of the supporting Excel file and take your time to make sure that you understand each element.
My reflections on the challenge
On the face of it, this seems like a simple challenge, with relatively straightforward logic.
It is pretty straight forward to solve using helper columns. The PowerQuery solution is also straightforward.
It becomes more difficult when you try to solve the challenge in one cell with one formula. This is because we are attempting to count the number of items in an array, which is not as simple as it first seems.
As you will see in Giles' live attempt, he looked at various approaches before settling on the solution using LET.
It is important to highlight that we are not saying this is the correct solution (or that focusing on single cell solutions is the right decision overall), it is just where Giles ended up in the live attempt.
The neatest solution that I found to this challenge was this formula from @babisflou87:
=SUM(--((LEN(A2:A20)-LEN(SUBSTITUTE(A2:A20," ","")))=1))
I explain how this formula works in the babisflou87 Solution tab of the supporting Excel file.
What is the Excel BI Challenge series?
The Excel BI Challenges series has been created by Vijay A. Verma. It is an (ever-growing) set of Excel-based challenges. Each challenge is designed to test your mastery of Excel-based modelling.
