Long but Less formulas or Short but More Formulas

[Statto]

I was wondering if anyone knew the performance difference between using shorter formulas but more cells, or longer formulas with less cells.

I am working on a new iteration of my workbook that already contains a lot of nested formulas doing the background work. There is heavy use of IF/IFS, INDEX and MATCH, as well as AGGREGATE and SUMPRODUCT. I have avoided arrays and volatile formulas deliberately to avoid any slowdown. Some of the formulas are 100+ lines long.

Ideally I'm trying to reduce the original workbook to as few cells as possible to help with future updating. Something I have learnt is, on balance, longer formulas and less cells are easier to work with in the long run, especially when you are dealing with so many precedents and dependencies. It can be difficult working with cells that have dependencies 100 columns away.

I can't say for certain so far if my workbook is calculating faster.

What is your preference?

[Pete_UK]

Although there is sometimes a need for a long formula, I favour the use of helper columns, where you can have simpler formulae which are easier to maintain in the future. They often calculate more quickly, although it does mean that more memory is used (generally). Suppose you have an INDEX/MATCH formula, which is copied across several columns to get data for different fields, but all on the same row. It is more efficient to put the MATCH part of the formula in one column, so that it is only calculated once, and then the INDEX part only needs to reference that column, rather than duplicating the MATCH across all cells in those columns.

Also, you say that you have avoided array formulae, but AGGREGATE and SUMPRODUCT functions are in themselves array functions - it's just that you don't usually have to use Ctrl-Shift-Enter to commit them. COUNTIFS and SUMIFS can often be used instead of SUMPRODUCT, and are more efficient.

Multiple IF/IF formulae can often be simplified by using lookup functions and a lookup table, and I would hate to try to decipher a formula which is 100+ lines long !!

Hope this helps.

Pete

[loginjmor]

I agree with Pete. Shorter formulae are less likely to have bugs, mistakes, and logic errors.

[Statto]

I did suggest a while ago to the Excel Twitter page that they should consider a way of writing comments within formulas, to aid with understanding each part. They did respond to me by saying they'd pass on the idea to the development team.

I know that Alt + Return forces a line break, which certainly is the best current way of making a long formula readable.

[FDibbins]

I echo Pete's thoughts on this.

Helpers can reduce the need for long-winded, repetitive functions within formulas.
Shorter formulas are almost always easier to understand, decode, debug and adjust
They make more sense when you come back later (a year or more) and need to figure out what you have done.
(I have had, what I thought at the time where, fancy involved formulas, but when I looked at them some time later, I had no idea what I had done, and wondered if it was even 1 of my formulas lol)

[Fluff13]

I also agree that shorter is "better", also if you are using a lot of IFS (as opposed to nested IF) then you would be advised to read this, especially if you have complex formulae inside
http://excelmatters.com/2019/03/19/i...-if-functions/

[Hydraulics]

There is an interesting comparison between a set of small formulas and a single "Megaformula" in J. Walkenbach's "Excel 2013 Formulas", chapter 21.

The bottom line is that one single formula is usually faster and the file size is smaller.

I usually prefer readability over speed, unless the layout of the worksheet rules out the use of helper columns/cells.

HTH,

Francesco

[Statto]

There is an interesting comparison between a set of small formulas and a single "Megaformula" in J. Walkenbach's "Excel 2013 Formulas", chapter 21.

The bottom line is that one single formula is usually faster and the file size is smaller.

I usually prefer readability over speed, unless the layout of the worksheet rules out the use of helper columns/cells.

HTH,

Francesco

Cool, I'll have to check that out!

I just think that when you have a large project, you can end up spending a long time making changes — and that is as a result of having so many cells. It's unavoidable a lot of the time, but I personally prefer to work with mega formulas instead of spreading it out.

I actually built a version of my workbook in R using the Shiny package, and apart from the initial difficulty of getting everything working correctly, it took far less code than the amount of formulas it needed in Excel.

I also agree that shorter is "better", also if you are using a lot of IFS (as opposed to nested IF) then you would be advised to read this, especially if you have complex formulae inside
http://excelmatters.com/2019/03/19/i...-if-functions/

I did not know that. There is a case where I used IFS in a very long formula, not knowing that it was less efficient than multiple IF statements. I will be changing it.

[Fluff13]

You're welcome & thanks for the feedback

[Statto]

This thread ties in with another question: does anyone know why Excel is still so slow for larger workbooks? I have a MacBook Pro 2018 15" with 32GB RAM and an i9 processor — and I use Parallels as a virtual machine so I can run the Windows version of Office. I still suffer from the same slowdowns that I had many years ago though, with the 'Calculating (2 Threads) ##%' message appearing sometimes for 12 seconds before the table loads. It still baffles me that Microsoft are adding all these new functions, but they can't seem to make Excel significantly faster despite the advances in hardware.

[TMS]

I did suggest a while ago to the Excel Twitter page that they should consider a way of writing comments within formulas, to aid with understanding each part.

Maybe ...

Please Login or Register  to view this content.

[FDibbins]

Trevor, that returns an error for me?

	R
1	=A1+B1+N("this adds a1 and b1")
2	#VALUE!

[FDibbins]

Trevor, that returns an error for me?

	R
1	=A1+B1+N("this adds a1 and b1")
2	#VALUE!

Scratch that, A1 and B1 each contained text.

Handy trick to know

[TMS]

Handy trick to know

Not something I use very often. Can't remember where I picked it up but, as you say, neat trick.

[Pete_UK]

I've never used it, but I came across it many years ago, maybe on the old newsgroups.

Pete

[Statto]

It's a useful trick, but I'd still like to add comments that are not part of a formula.

[TMS]

Well, you can add comments to a cell but, AFAIK, not to a formula. Not even sure how that could be achieved.

[Statto]

Say you have a tab in your workbook containing a 'database' — the main table in your project that is regularly queried. It could be any size, but as we all know, Excel can struggle with even relatively small tables. How bad is making unnecessary references to this table? I'm talking about excessive use of INDEX and MATCH to retrieve column data.

I have a table consisting of 5,000 rows. Even this is taking a few seconds for certain tasks to load in my worksheet. Although I suspect I have used INDEX and MATCH more times than necessary in places, instead of reusing the cells that already contain a relevant INDEX and MATCH output.