Minimum 10, Maximum 10 from a series

[SarahKC]

Hi there,

I have data whereby I would like the sum of the top 10 numbers in a row and the sum of the bottom 10 numbers in a row.

I have done this before by adding another row with ranking and then doing an If statement referencing the ranking.

I want to do this operation, however, over many rows so ranking would be a pain and sorting by each row and then doing it would also be a pain.

Thoughts?

Thanks,
Sarah

 

[schielrn]

Welcome to the Board!

What if there is a tie for the 10th spot, how do you want that handled?

Example being ththis data:

1
2
3
4
5
6
7
8
9
10
10
11
12
13

Do you want to sum the 10 twice?

 

[SarahKC]

Thanks for the welcome.

Great question: no. I want one ten.

 

[schielrn]

Ok that makes the formula a little longet, but this should work for the bottom ten:

=SUM(IF(A1:A15<=SMALL(A1:A15,10),A1:A15,0))-(COUNTIF(A1:A15,SMALL(A1:A15,10))-1)*small(A1:A15,10)

For the top 10:

=SUM(IF(A1:A15>=Large(A1:A15,10),A1:A15,0))-(COUNTIF(A1:A15,large(A1:A15,10))-1)*large(A1:A15,10)

Both of these are array formulas and need to be confirmed with control+shift+enter and not just enter. Hope that works.

 

[SarahKC]

Okay and now I have to figure out why this works so I can understand what I'm doing...