0

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!!!

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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, 2010 at 17:17
  • make that: =SUM(SMALL(mydata,ROW(1:100)))
    – Doug Glancy
    Apr 11, 2010 at 0:12
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .