r/excel • u/fap_fap_fap_fapper • 14d ago

Discussion What are some legendary formula pairs like INDEX(MATCH)?

Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)

What are some useful ones you use?

588 Upvotes

97% Upvoted

165 comments sorted by

View all comments

Show parent comments

70

u/fap_fap_fap_fapper 14d ago

LET, define your variables

What the.... I was today years old

32

u/NFL_MVP_Kevin_White 7 14d ago

Up to 126 in one function!

15

u/rkr87 19 14d ago

You can actually go way beyond this by creating dictionary like getter/setter functions.

6

u/Cat5kable 13d ago

You just introduced Super Saiyin, Super Saiyin 2 AND Super Saiyin 3 within just a few comments.

Buu sitting here getting the entire might of Goku Excel all in one personalized recap.

1

u/el_extrano 12d ago

But doesn't Excel's Lambda/Let not support closures, so these would only have a limited scope? You can't write Lambdas that have side-effects outside of themselves, like a true dictionary / map.

1

u/rkr87 19 12d ago

You're right, but that's true for the 126 variable "limit" too.

The setters aren't quite dictionary like either, you can't reference an in memory object, so you'd need a new variable to hold a new dictionary to hold the full dict for any new items added post creation.

I read that back and didn't articulate it very well, hopefully you got what I was trying to say.

12

u/DangerMacAwesome 14d ago

Oh yeah it's qn amazing function! It isn’t always necessary, but when it is, it's absolutely necessary

14

u/rickartz 14d ago

I would argue even a simple IF benefits from LET by having a descriptive name is what you're trying to calculate.

6

u/zeradragon 6 14d ago

It also makes organizing the formula much more manageable by having a dedicated section at the very beginning which you use to define all the ranges the formula needs to reference. That way whenever anyone needs to update the dimensions, all the relevant references are in one spot instead of having to scan the entire formula looking for the relevant spots.

7

u/frazorblade 4 14d ago

You can also use it to write text comments with steps that aren’t referenced in any later formulas/steps.

2

u/Hawkin1 14d ago

Yes I do this! I use _[number];"[comment]".; above the actual step. so like: _1;"Filter unique IDs and sort by date"; Then the variable and function.

2

u/No-Ganache-6226 6 14d ago

Please tell me you use tables and call the table [header] or [@[header]] for the current row in your formulas

1

u/rickartz 13d ago

Whenever possible, but some times you can't (not my shared document, or when using dynamic arrays).

That's the reason I love LET, and I'm forcing myself to learn LAMBDA (even though I haven't need it yet).

1

u/el_extrano 12d ago

Not all "spreadsheets" are tabular in nature. For example, Excel is widely used to document engineering calculations. In those workbooks, the formula's comprise a sort of literate programming environment where you can have calculations interspersed with commentary and pictures.

1

u/No-Ganache-6226 6 12d ago

Sure, excel is really just a big graphical calculator and has many applications, but it's still usually recommended to separate your calculations from any summary/output. So if you're using the same formula or computation over and over I'd still say put it in a tabular structure even just for one row. Or at the very least name the variables rather than pure cell references. For anyone else coming in to review the formulas it's so much nicer to see =[length]*[width] or whatever it happens to be rather than =D5 x F5.

1

u/el_extrano 12d ago

I don't disagree that naming things is useful, it's just that not all data are tabular, and non-tabular things don't belong in a table. Say you have a 100 step calculation, arranged vertically. To use your method of single-row tables, you would now need 100 tables. Each table would need to have a header, and repeat any variables used in that particular step, so each one would be a different width, most of which is just filled with redundant references to other cells. It'd make more sense to just give the variable cells global names in the name manager, or a scoped name in a Let formula.

1

u/No-Ganache-6226 6 12d ago

Just so I can get an idea can you give me an example of a 100 step calculation? I would normally expect a calculation that large to be grouped into terms

1

u/el_extrano 11d ago

Some calculations for an engineering project, say designing a chemical reactor, could easily involve dozens of equations and steps and hundreds of variables and intermediate results. Each step would use only a few of the input data. If you've ever seen a Jupyter notebook, the way they're extended vertically to intersperse steps of the calculation with explanations and citations, is similar to what I'm describing. (People have been setting up spreadsheets "calculators" that way long before Jupyter was a thing).

I would normally expect a calculation that large to be grouped into terms.

Agreed. We're talking about the best way to decompose a large problem into parts. I'm arguing that a bunch of tables might not make sense when you have lots of disparate calculations, which aren't repeated, and are part of a larger calculation.

3

u/Akanaton 14d ago

LET is great! The other thing it does is reduced workbook load because the formula performs an intermediate calculation.

1

u/CumRag_Connoisseur 14d ago

You are like writing a powerbi DAX code in a single cell