fbpx

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

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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:

students-and-gpas

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.

dancing legoman

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…

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

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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

  1. MH says:

    Can we include an "array" as well?

  2. David N says:

    If we're using Excel 365, then XMATCH (or XLOOKUP) can perform an ascending or descending search without actually having to sort the data. So you don't even need the MAX function to find the highest GPA.

    =INDEX(C6:C45,XMATCH(99^9,D6:D45,-1))

    And since we're already using Excel 365, SORTBY and SEQUENCE can handle getting the top five.

    =INDEX(SORTBY(C6:C45,D6:D45,-1),SEQUENCE(5))

  3. Craig says:

    I have a list of work orders with different line numbers that have individual statuses. I am trying to return a list of all work orders (no line numbers) in which ALL line numbers share the same status. I have tried to use filter and no luck. I have tried to use xlookup to simply return the work order if all work orders have same status. I cannot figure this out. Any help?

    tried =xlookup(wo#,wo#=@wo#*status="d",wo#,"",0,1) and it is not giving me the result

Leave a Reply