Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there any way I could sum up the lowest 100 values within a range?

I know that min() would give you the lowest value but i need something to return the 100 lowest values.

I just used 100 hypothetically to make it easier to understand what I'm trying to achieve.

I can use any of the following spreadsheet apps: Openoffice.org, Excel or Google Spreadsheets -- whichever works.

Thanks a lot!!!

share|improve this question

2 Answers 2

Supposed that your data is in range named mydata, you have to use the following formula:

=SUM(SMALL(mydata,ROW(A1:A100)))

entered by Ctrl+Shift+Enter.

share|improve this answer
    
I always have to be reminded about the trick of using ROW to create an array like this. Very nice. I noticed that it can be slightly simplified: =SUM(SMALL(mydata,ROW(1:3))) –  Doug Glancy Apr 10 '10 at 17:17
    
make that: =SUM(SMALL(mydata,ROW(1:100))) –  Doug Glancy Apr 11 '10 at 0:12

If an Excel macro would work then it would be easy and only take a few minutes. Record a new Macro (Tools-Macros-Record). Copy the column of values into a new column. Sort the column, in a nearby cell store the sum of the lowest 100 using a formula (=F1..F99). Copy the cell containing the needed value, then in a separate cell, do a Paste-Special and select Paste Values. This final cell contains the sum by value. Next, delete the cell containing the sum by formula, then delete the temporary column. Stop the Macro recording.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.