Eliminating the high 4 values

D

Dick Gwin

Need help with a formula which sums the values in a range eliminating the 4
high values in the range.
Excel 2000- Thanks in advance
 
G

Gaurav

There may be a better way but this works.

Assuming your range is A1:A10. you can change it as per the need.

=SUM(A1:A10)-SUM(LARGE(A1:A10,1),LARGE(A1:A10,2),LARGE(A1:A10,3),LARGE(A1:A10,4))

Does that help?
 
G

Gord Dibben

Shorter version.

=SUM(A1:A10)-SUM(LARGE(A1:A10,{1,2,3,4}))


Gord Dibben MS Excel MVP
 
Top