How do you deal with large, hard-to-read Excel formulas?

Question

When Excel formulas get large, I often lose track of what&rsquo;s actually happening.I&rsquo;m wondering whether representing formulas structurally (instead of plain text) could make them easier to read and modify, but I&rsquo;m not sure if this really helps in practice.How do you usually handle large formulas?

anigbrowl · Accepted Answer

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.

Someone1234 · Answer

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.

clwncr · Answer

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.

harperlee · Answer

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

There's more!

DerArzt · Answer

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.