How to write complex Excel formulas (hint: it’s a lot like LEGO)

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.

1. We can easily lookup the name of a student with GPA of say, 3.53, using lookup formulas like XLOOKUP.
2. 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:

=XLOOKUP(MAX(D6:D45),D6:D45,C6:C45)

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:

=XLOOKUP(LARGE(D6:D45, 2),D6:D45,C6:C45)

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…

• Break down the complex problem to small manageable chunks
• Write down formulas for these chunks
• If you need to repeat some logic, consider using either helper cells, or the new LET() function
• Utilize the power of Excel references (absolute - $A$1, relative A1, mixed - A$1, $A1 and finally structural (table[column]) to write shorter & simpler formulas
• Try newer formulas like FILTER, UNIQUE, SORT, LAMBDA etc. to get results easily
• Take time to learn new techniques - such as spill ranges, LAMBDAs, data type driven formulas
• When you have time, challenge yourself to figure out another formula to solve the problem

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

If you would like to practice these formulas, here is the sample workbook. 

Up for a challenge? 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.

• Formula Forensics – a series of amazing Excel formula trickery
• Multi-condition Lookups – using good old VLOOKUP in creative ways
• 10 Advanced IF formula tricks – the trusty IF can do wonders
• Dynamic Array Formulas – HOT HOT HOT – You must try these.

The post How to write complex Excel formulas (hint: it’s a lot like LEGO) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

source https://chandoo.org/wp/how-to-write-complex-excel-formulas/