Forum Discussion
Formulae
How do I find the the sum of the top * numbers in a given range
With 365, the formula is readable
=LET(
topN, TAKE(SORT(range,,-1), n),
SUM(topN)
)To see the workings this could be
=LET(
topN, TAKE(SORT(range,,-1),n),
VSTACK(topN, SUM(topN))
)
- Just tried this, and it doesn't work. My queries are, please:
1. Why is the formula split over four columns?
2. Should there be spaces within the formula?
3. Why are there two versions?
4. Why is 'n' capitalised after 'top', but not when it is on its own, particularly as n is a value, not text?
I look forward to your replies
Thank youSorry I didn't pick up your questions at the time.
1. Why is the formula split over four columns?
It turns difficult-to-read nested formulas into a sequence of statements which develop the logic step by step.
2. Should there be spaces within the formula?It is simply for readability. Just as this discussion is expressed in terms of words and paragraphs, code can be clearer to read with appropriate layout. The spaces make no difference to the the computer, it is entirely for human consumptions.
3. Why are there two versions?The first just returns the total as requested. The second gives more information in that it lists the 4 largest values along with the sum. It would also be possible to allow the end user to select the output they wish to see by simply setting an optional parameter.
4. Why is 'n' capitalised after 'top', but not when it is on its own, particularly as n is a value, not text?
I use a personal naming convention in which variables are defined using lower case (for longer names involving multiple words, I use camelCase in which subsequent words start with a capital letter). The topN treated the n as separate from the word 'top'.
