Named ranges! Also newer Excel versions provide you the LET function, which is neat for documentation (naming) of intermediate steps:
=LET(data; F4:F34;
dummy1; "This is a made up average function; this string is ignored";
dummy2; "one limitation is that you cant really overwrite variable names";
tally; COUNTA(data);
total; SUM(data);
total/tally)
It also avoids recomputing intermediate stuff. You can also encapsulate things in a LAMBDA anonymous function:
=LET(data; F4:F34;
mymeanfunction; LAMBDA(data;
LET(tally; COUNTA(data);
total; SUM(data);
total/tally));
mean1; mymeanfunction(data);
mean2; mymeanfunction(OFFSET(data;0;1));doc;"FIXME, this should have its own variable name to refer explicitly to G4:G34";
AVERAGE(mean1;mean2))
You could also move the LAMBDA to the name registry and use that function similarly to how you would use a named range or a built-in formula!
Coming from a lisp background, I was ecstatic to see this, but they have heavy technical limitations. I did play a little bit though with these concepts and the dynamic array functions. Fun functions to explore:
- DROP, TAKE, CHOOSECOLS, CHOOSEROWS to cut arrays
- INDEX, OFFSET, COLUMN, ROW to navigate
- WRAPCOLS, WRAPROWS, TOROW, TOCOL, VSTACK, HSTACK, TRANSPOSE to shape arrays
- MAP, SCAN, REDUCE to compute on top of arrays
I discovered named ranges only a couple of months ago, and they’re pretty amazing, as is the spilling behavior that fills an entire column from one cell. When it comes to the specifics of dealing with complex formulas, I usually copy paste them to a text editor because the formula bar doesn’t have a great editing ui.
How far did you get with let and lambda? It seems like you could build whole programs on them, but I haven’t pushed very hard on them yet. Do you want to say more about the “heavy technical limitations”?
Yes, I don't think Excel product owners are focusing on professional development, just power users. So things like the formula bar are lackluster. There is also a formula stepper that's mostly useless for debugging.
I played with them and even tried to build a minikanren, but on my day to day job I just use them tactically, so I avoid their limitations and I'm quite happy with them for that.
From memory:
- Recursion is severely limited in lambda.
- There are extra pains when putting formulas on the name manager or on conditional formatting (although more focused on references than on lambdas in particular, usage of lambda would be more powerful if they didn't exist)
- You can't do arrays of arrays, so your data structures need to be flat
Yeah, I agree — LET and LAMBDA are surprisingly powerful.
They definitely help with naming intermediate values and avoiding recomputation.
The limitation I keep feeling is less about expressiveness and more about
working with existing formulas:
debugging, making small changes, or explaining intent to someone else.
Even with LET/LAMBDA, the structure still lives mostly in a single line of text,
and the formula bar UI doesn’t really help you reason about that structure.
That’s the gap I’ve been curious about exploring.
Depending on how many levels of indirection there are (i.e. how many formulas reference other cells/the results of other formulas in other cells) I've found that using "trace precedents" and "trace dependents" in the formulas tabe to be helpful.
I use named ranges to semantically label relevant cells or ranges, and then use those labels in the formula. It's much easier to read and construct formulas when you don't have to think about where a value exists in the worksheet.
Break them down into multiple smaller formulas, with a column above explaining what they each do. Then consume the previous result in the next formula. This doesn't even need to be on the same sheet as the actual primary consumer sheet.
Yeah, that’s usually what I do as well.
Breaking formulas into helper columns definitely makes things clearer.
What I’ve been running into is cases where large formulas already exist
(and refactoring them into multiple columns isn’t always an option),
so I started wondering whether a structural representation could help
with understanding and small edits, rather than replacing that approach.
I’m not convinced it’s better yet — just exploring the space.
Break them into components and calculate the result iteratively if you can; I prioritize clarity and provability over raw performance. If it's a set of standard formula specific to an industry, consider lambda functions.
Named ranges! Also newer Excel versions provide you the LET function, which is neat for documentation (naming) of intermediate steps:
It also avoids recomputing intermediate stuff. You can also encapsulate things in a LAMBDA anonymous function: You could also move the LAMBDA to the name registry and use that function similarly to how you would use a named range or a built-in formula!Coming from a lisp background, I was ecstatic to see this, but they have heavy technical limitations. I did play a little bit though with these concepts and the dynamic array functions. Fun functions to explore:
There's more!I discovered named ranges only a couple of months ago, and they’re pretty amazing, as is the spilling behavior that fills an entire column from one cell. When it comes to the specifics of dealing with complex formulas, I usually copy paste them to a text editor because the formula bar doesn’t have a great editing ui.
How far did you get with let and lambda? It seems like you could build whole programs on them, but I haven’t pushed very hard on them yet. Do you want to say more about the “heavy technical limitations”?
Yes, I don't think Excel product owners are focusing on professional development, just power users. So things like the formula bar are lackluster. There is also a formula stepper that's mostly useless for debugging.
I played with them and even tried to build a minikanren, but on my day to day job I just use them tactically, so I avoid their limitations and I'm quite happy with them for that.
From memory:
Yeah, I agree — LET and LAMBDA are surprisingly powerful. They definitely help with naming intermediate values and avoiding recomputation.
The limitation I keep feeling is less about expressiveness and more about working with existing formulas: debugging, making small changes, or explaining intent to someone else.
Even with LET/LAMBDA, the structure still lives mostly in a single line of text, and the formula bar UI doesn’t really help you reason about that structure. That’s the gap I’ve been curious about exploring.
Depending on how many levels of indirection there are (i.e. how many formulas reference other cells/the results of other formulas in other cells) I've found that using "trace precedents" and "trace dependents" in the formulas tabe to be helpful.
I use named ranges to semantically label relevant cells or ranges, and then use those labels in the formula. It's much easier to read and construct formulas when you don't have to think about where a value exists in the worksheet.
Break them down into multiple smaller formulas, with a column above explaining what they each do. Then consume the previous result in the next formula. This doesn't even need to be on the same sheet as the actual primary consumer sheet.
Yeah, that’s usually what I do as well. Breaking formulas into helper columns definitely makes things clearer.
What I’ve been running into is cases where large formulas already exist (and refactoring them into multiple columns isn’t always an option), so I started wondering whether a structural representation could help with understanding and small edits, rather than replacing that approach.
I’m not convinced it’s better yet — just exploring the space.
Break them into components and calculate the result iteratively if you can; I prioritize clarity and provability over raw performance. If it's a set of standard formula specific to an industry, consider lambda functions.