CODEWITHSUNDEEP
excelexcel-formula
peterhurford
peterhurford

Reputation: 1102

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

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?

Upvotes: 4

Views: 2002

Answers (3)

Joanna Bryson
Joanna Bryson

Reputation: 103

Thanks for this, the answer helped me solve an even more heinous problem:

=AVERAGE(LARGE(CHOOSE({1,2,3,4,5,6,7,8},(P3),(R3),(T3),(V3),(X3),(Z3),(AB3),(AD3)),ROW($1:$4)))

which let me average the largest of nonadjacent columns for a bunch of students.

  • you need to push ctrl shift enter to evaluate such an expression (it's building a table on the fly) – that will wrap curly brackets {} around it.
  • the ()s around a cell reference mean that if it's blank (say a student didn't do a quiz) it gets translated as 0 not #fail
  • the dollar signs $ are necessary or you can't copy & paste this into other rows and get the relative stuff to all work.

Not that you asked but I figured someone might...

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166196

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

Upvotes: 8

David
David

Reputation: 137

=IF(AND(A4+A5>=A6+A5, A4+A5>=A3+A4),A4+A5,0)

you could use this formula to find the biggest two numbers in a row. and this would equate to max2

place your example range in column a and place the formula in b4. drag this from b:1-B:N and the number in b will correspond to the sum of the two highest cells that are next to each other

so if you had n=n you could do if(and(sum(a2:An)>=sum(a1:A(n-1), sum(a2:An)>=sum(An:A(n+n),sum(a2:an),0)

Upvotes: 0

Related Questions