Do you want to know the secret to building complex, advanced Excel formulas? Here it is…
Get inspiration from LEGO.
Confused? Let me demonstrate.
Calculating Student with Highest GPA
Let’s build a formula to calculate the student with best GPA out of a table like this:
Our data is in columns C& D, in the range C6:D45.
- We can easily lookup the name of a student with GPA of say, 3.53, using lookup formulas like XLOOKUP.
- We can also calculate the maximum (best) GPA using the MAX formula.
When you combine the Ideas 1 & 2, just like you combine LEGO, you create a third formula that gets the name of student with highest GPA.
So the final formula would be:
And now, you can do the happy dance.
How about second highest GPA Student?
We can extend this idea to get the name of student with second highest GPA. Instead of MAX, we need to use LARGE function. Like this:
In the above formula, LARGE(…, 2) returns the second highest GPA.
You can use the same concept to get third highest or second lowest (use SMALL(…,2)).
The LEGO way of building complex formulas…
Just as there are a few basic building blocks in LEGO (well, nowadays, you can find a weird block shaped like dragon’s eyeball to fit in your harry potter set, but you get my point), we can also construct a complex formula from few basic ingredients.
Keep this construction tips in mind…
5 Examples of how to build complex formulas
If you are curious about this, then definitely watch my video. It shows how to write advanced formulas with Excel. See it below or on my YouTube channel.
Sample Workbook – 5 Complex Formulas
Then, go ahead and write a different formula for top 5 students with GPA.
Share your answers in the comments section.
Want more inspiration? Check out below examples:
There are heaps of advanced formula examples on chandoo.org. Start with below and see how you can twist the same 2×2 block in umpteen ways.