Everything you need to master Excel BI Challenge 2
The challenge
This is the second challenge in the Excel BI challenge series. We are asked to provide a formula to sum the last 3 non-zero values in the range A2:A10.
The answer should be 44
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 an Excel Tables-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 sum the last 3 non-zeros values in the range A2:A10.
To sum up the last three non-zeros we are going to need to ignore or filter out any zeros in the data.
Once we have achieved that we need to find a way to identify the last three values in the range.
We then need to sum up those three numbers.
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 - Filter out any zeros in the data
We need to filter the data in the range A2:A10 to remove any zeros:
=FILTER(A2:A10,A2:A10>0))
This uses the FILTER function to filter the data to return only values greater than 0
Logic element 2 - Identify the last three values in the range
If you aren't aware of the TAKE function then this part of the challenge would be very difficult.
Using the TAKE function it is nice and simple.
=TAKE(C2#,-3)
Here we use the TAKE function over the range from element 1 to choose the rows to include. Using -3 takes the last three rows from the data.
Finally, let's bring it all together and sum it up:
Bringing the two elements from above together and wrapping it in a SUM gives us the following formula:
=SUM(TAKE(FILTER(A2:A10,A2:A10>0),-3))
As long as you know the FILTER and TAKE functions this is a pretty simple challenge. To prove that point Giles solves it in just 2 minutes!
Don't worry though, in the WALKTHROUGH VIDEO Giles then explores a number of other functions looking for other solutions and takes a look at a few other people's solutions to the challenge. The workings for this is all documented in the supporting Excel file.
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 seemed like a simple challenge, with relatively straightforward logic.
And,in fact, it was.
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.
Another neat solution, very similar to Giles' but using LET was the formula below from the elusive Victor Momoh:
=LET(a,A2:A10,SUM(TAKE(FILTER(a,a<>0),-3)))
I explain how this formula works in the Victor Momoh 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.
