Trick to Complex Excel Formulas

There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarize or extract data from poorly structured workbooks. I’m not talking about relatively simple nested functions like INDEX and MATCH, I’m talking about monsters like this:

=IF(ISERROR(INDEX(Sheet1:$E$162,SMALL(IF(Sheet1:$E$162="ABC",ROW(Sheet1:$E$162)),ROW(1:1)),4)),"",INDEX(Sheet1:$E$162,SMALL(IF(Sheet1:$E$162="ABC",ROW(Sheet1:$E$162)),ROW(1:1)),4))

And as we press CTRL+SHIFT+ENTER on massive array formulas like the one above we realize we’ve reached the echelon of Excel Guru.

But have we? For a while, we think so. And we should be proud, after all, formulas like that are not for the feint hearted. It shows we’ve achieved the level of black belt formula writing that most will never attain.

However, in our effort to handle more and more complexity through formulas we’ve failed to see that the problem was never in our lack of ability to write these formulas in the first place. It was that we allowed ourselves and others to structure workbooks in a way that meant we had no other choice.

For the true Excel Guru knows that if the data were in a Tabular format we’d be able to use the amazing tools Excel has available. Tools that make mincemeat of complex formulas like the one above. For example:

  • PivotTables
  • Aggregation functions like:
    • SUMIFS
    • AVERAGEIFS
    • COUNTIFS
    • AGGREGATE
    • SUBTOTAL
    • MIN/MAX/SMALL/LARGE
    • DSUM/DAVERAGE/DCOUNT etc.
  • Lookup functions like:
    • VLOOKUP/HLOOKUP
    • INDEX

You see the Excel Guru knows that poorly formatted workbooks that require complex formulas to aggregate, analyze and extract data are error prone, often slow to respond and typically suffer from MB bloat.

So if formulas like the one above scare the bejesus out of you then fear not. All you need to learn is the correct way to structure your data and then you can use the built-in tools the way they were intended.

Say No to Complex Excel Formulas

Here at MOTH we often get asked for help with writing complex formulas. I used to take on the challenge of writing monster formulas to solve these problems and accommodate poorly structured files. But nowadays I prefer to encourage a better data structure and easy solutions because when things are easy you can do them yourselves and they’re easily handed on to the next person who inherits your workbook.

Now you might be thinking:

“where’s the job security in that?”

And you’re right. If you make things easy for everyone to use then you’re more easily replaced. But the flip side is if you make things easy to use and help others look like Excel Rock Stars then everyone is going to think YOU ARE THE EXCEL GURU and you will be indispensable ?

Now not all complex formulas are the result of poor workbook layout. Some array formulas are the best or only solution, but when there is a way to achieve the end result more easily by reformatting your data, then I encourage that every time.

Common Challenges Solved without Complex Excel Formulas

 

Excel Challenge Complex Solution Simple Solution
Extracting a subset of data from one sheet to another Nested function array formula PivotTables to Extract data
Summarizing data spread over multiple sheets (3D Formulas) 3D SUMIF/S array formula Tabular data and SUMIFS/AVERAGEIFS/COUNTIFS etc. or even PivotTables.
Extracting a unique list of values Array formula PivotTables automatically create a unique list of items in row labels. Or you can useAdvanced Filter.
Multiple sheet VLOOKUP formulas 3D VLOOKUP array formula Tabular data and a regular VLOOKUP or PivotTables

;

So, next time you’re faced with a complicated formula you should ask yourself if changing the layout of the workbook could be the solution instead of writing a mind-bending formula that is likely to end up broken the minute a less experienced Excel user tries to edit it.

Note: this may also apply if you’re reaching for ATL+F11 to open the VBA editor every time you come across something you can’t solve with the built-in Excel functions and tools.

For comprehensive Excel Formulae Course, Click here.

Team Excelgoodies – www.Excelgoodies.com

2 thoughts on “Trick to Complex Excel Formulas

  1. no credit check loans

    Does your website have a contact page? I’m having problems locating it but, I’d like to send you an e-mail. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it develop over time.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *