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'.
=SUM(LARGE(B1:B24,ROW(A1:A7)))
Let's say the given range of numbers is in B1:B24 then you can apply this formula to calculate the SUM of the largest 7 numbers. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
If you want the SUM of the largest 9 numbers you can change A1:A7 to A1:A9.
- Sorry, but this doesn't work as the system is picking up A1:A7 as the range A1:A7. I copied and pasted your solution to ensure that I did not make any typos.
=SUM(LARGE(B1:B24,ROW(A1:A7)))
ROW(A1:A7) evaluates to {1,2,3,4,5,6,7} which means that range A1:A7 isn't picked up by the formula. You can actually enter any data in A1:A7 such as formulas, text or numbers and verify that the formula doesn't refer to the entries in this range.
