Select data from list

**cdrr** · 08-11-2010, 07:09 PM

I'm trying to figure out how to select the 10 lowest numbers from a set of 20 data points in Excel 2002. Can anyone help? Thanks in advance.

Rick

**JBeaucaire** · 08-11-2010, 07:22 PM

Try the SMALL() formula.

**Palmetto** · 08-11-2010, 07:26 PM

Assuming you will start on row 1 of some column and the data is in range A1:A20

=small($A$1:$A$20,row())

copy the formula down the column for ten rows.

**cdrr** · 08-11-2010, 08:01 PM

ok, let me be a little more clear...don't think the small command is going to wrk.

i've got 20 data points arrayed from b2 thru u2...i want to then sum the lowest 10 data points of those 20 in cell v2. hopefully that's a little more clear idea of what exactly i'm trying to do.

thanks again.

rick

**Palmetto** · 08-11-2010, 09:08 PM

=sumif(b2:u21,"<"&large(b2:u2,11))

**cdrr** · 08-11-2010, 09:31 PM

Palmetto--thanks for your help...copied it directly...doesn't work...i did change the u21 too u2...i want to use it on a single row...it returned 0...tried it w/o the 11 on the end (couldn't figure out what that was...thanks for the effort.

**Palmetto** · 08-11-2010, 09:40 PM

The formula works for me, though I only used about 15 values, randomly entered. The U21 was a typo.
The "11" in the formula specifies that only values less the the eleventh largest value are to be summed. See the Excel Help on the LARGE function.

Upload a sample workbook.

**cdrr** · 08-11-2010, 09:53 PM

Thanks again Palmetto...I think "we're" getting closer but it still doesn't work like it should altho it does return a value...i think the problem may be that there are numerous duplicate values...like 3 85s, 4 87s and it needs to count all of them if they are in the 10 lowest...and, there may be say, 3 90s but only 2 sholuld be counted, i.e. there are 8 scores lower than 90 and then 2 of the 3 90s need to be counted.

how do i upload a worksheet...sorry for the newbie questions.

rick

**Palmetto** · 08-11-2010, 10:03 PM

Perhaps it would be best if you sorted the row from left to right in ascending order (smallest to largest).
Then B2 thru K2 would contain the 10 lowest scores, so: SUM(B1:K1)

To upload a worksheet, click EDIT POST under one of your last posts, click Go Advanced, then scroll down to Manage Attachments.

**cdrr** · 08-11-2010, 10:13 PM

attached spreadsheet to previous post...only problem with sorting is they need to be in chronological order...once i sort, i lose that.

thanks again.