Finding Lowest 5 numbers in a range

[onthegreen03]

Hi - I have numbers in cells A8:A65 and I am looking for a formula that will return the lowest 5 numbers in that range. I tried this formula but it did not work: =SMALL($A$8:$A$65, ROWS(BE$45:BE45)). Cell BE45 is where I want the first result to land, then I will drag that formula down 4 more rows. What am I doing wrong? I should mention that some of the cells in A8:A65 can be blank so I'd want the formula to ignore the blanks. Any help would be appreciated. Thanks in advance!

 

[Fluff]

How about in BE45

=SMALL(A8:A65,SEQUENCE(5))

but clear the cells below of any formula.

 

[onthegreen03]

That did not work. It returned a zero, and the remaining rows filled in with 1, 2, 3, 4. A little bit more background in case it matters. In cells A8:A65 I have a ranking formula which references YTD sales by account in another column (AC). In that YTD sales column a "#N/A" will populate if no sales, so to get the rank formula to work I went with this formula: =IF(ISNA($AC8),"",COUNTIF($AC$8:$AC$65,">"&$AC8+1)). The ranking formula appears to be working, if there are sales it ranks them, and if there are "#N/A"'s in leaves that cell blank.

 

[Fluff]

It returned a zero, and the remaining rows filled in with 1, 2, 3, 4.

Considering the formula you have in Col A, that's exactly what I would expect you to get (assuming no ties), 0 to 4 will be the 5 lowest numbers.

 

[onthegreen03]

Sorry, I want the lowest ranked numbers, in this case the highest numbers in the range. Sorry about the confusion there.

 

[Fluff]

In that case just swap Small for Large.

 

[onthegreen03]

You rock Fluff!

 

[Fluff]

Glad to help & thanks for the feedback.