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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Link a text box based on drop down box with four options MS Word 365 0
Trying to find additional ranges outside of min, max, and average 2
VBA multiple list of values for variable 1
eliminate negative value from normal random number distribution 4
If value between 2 numbers 7
Getting sum of values in another sheet while using condition 5
Drop down list control value in vba 2
Use TODAY() function as the criteria in a SUMIF formula 1

Top