Forum Discussion

dlkosko1958's avatar
dlkosko1958
Copper Contributor
Jan 29, 2020

Sum of a range of cells, dropping the highest number in the range.

I would like to calculate the sum of a range of cells, dropping the highest number in the range as part of the calculation. How do I write this formula? Thanks!
Formulas and Functions
PReagan's avatar
PReagan
Bronze Contributor
Jan 29, 2020

Hello dlkosko1958,

 

Just another for fun:

=SUMPRODUCT(SMALL(array,{1,2,N}))

where N is the Nth largest value in the array.

 

For example, with N+1=5 numbers that would be:

 =SUMPRODUCT(SMALL(array,{1,2,3,4}))

 If N+1=13 numbers that would be:

 =SUMPRODUCT(SMALL(array,{1,2,3,4,5,6,7,8,9,10,11,12}))

This is an array formula so press Ctrl+Shift+Enter to execute. 

Resources