How do I get the max N numbers in row in Excel?

Question

MAX will take a range and tell me the largest number. But what if I wanted to iterate over that range and find the largest two numbers in a row?

For example, if I have the range [0, 2, 5, 6, 9, 3, 8], MAX is 9, but MAX2 is 15 (6+9). MAX3 is 20 (5+6+9).

How would I write MAX2, MAX3, or MAXN in Excel?

Answer 1

Eg: sum 3 largest numbers in A1:A5

=SUM(LARGE(A1:A5,ROW(1:3)))

This is an array formula, so you need to use use Ctrl+Shift+Enter