Forum Discussion
How to sum an array, after applying a few conditions to it? It's complicated, I'm attaching a file.
My argument would be "do you need to see the value?", "why is it important to you?" If there is no reason to see the intermediate values, then they merely constitute 'sheet junk', in much the same manner as extraneous ornamentation of charts is described a 'chart junk'.
Despite that, there are reasons of computational efficiency that might justify creating an array of intermediate results. Also, many users like to be able to check Excel calculations with a pocket calculator. For me, that is mis-directed effort. They would be better advised to create readable formulas and check those, because errors are (virtually) always to be found in the users' code, not the Excel evaluation.
Very often intermediate results are re-used multiple times. So a single formula tends to get bigger and takes more calculation time.
I know there is now LET() but I am not getting warm with it - yet.
If you really want to build a single formula you have to build helper columns first and then try to put them together.
Agreed. I probably glossed over "despite that, there are reasons of computational efficiency that might justify creating an array of intermediate results" too hastily.
That said, one of the benefits of the LET function is that the local names are not re-evaluated upon each use, so the computational efficiency is achieved without repainting the screen with intermediate results.
Yes, I did use helper ranges (scratch space) while I was developing parts of the formula. The final steps were the 'packaging' of the solution, but the process does not create the nested formula nightmares that results from developing traditional formulas without helper ranges.