Help Request #2

  • Thread starter Thread starter Monte Comeau
  • Start date Start date
M

Monte Comeau

OK, I have a range of numbers. I need to pick out the lowest 5 from the
range and put them in order from lowest up.

(Golf Scores)

I used the MIN function to pick out the lowest but how do I then pick out
the second lowest, third etc. The range is scattered all over the worksheet
so I cannot use the SORT function.
 
Hi Monte
you can use the function SMALL. So for your example try:
=SMALL(your_range,1)
....
=SMALL(your_range,5)
for the five lowest numbers

HTH
Frank
 
OK, I have a range of numbers. I need to pick out the lowest 5 from the
range and put them in order from lowest up.

(Golf Scores)

I used the MIN function to pick out the lowest but how do I then pick out
the second lowest, third etc. The range is scattered all over the worksheet
so I cannot use the SORT function.
Click to expand...

Use the SMALL function. For example:
=SMALL(A1:A15,1) will return the smallest value in the stated range,
=SMALL(A1:A15,2) will return the second smallest and so on.
 
Hi Monte,

Since your data is scattered over the worksheet the function Small won't
work (as far as I know).
I've therefore written a function in VBA which basicly does the same :

The difference is that in this function you first enter which smallest
number you want to get (1st ; 2nd ; etc) and then the cells from which you
want to take this minimum.

Opposite to the basic Small function you can't use THIS function as an array
function either.


Function SmallAcc(Number, ParamArray Arguments())
Dim Arr(), A As Integer, B As Integer, I As Integer
A = LBound(Arguments)
B = UBound(Arguments)
I = 0
ReDim Arr(A To B)
For I = A To B
Arr(I) = Arguments(I).Value
Next
SmallAcc = WorksheetFunction.Small(Arguments, Number)
End Function

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
In addition to my first mail : The values in this function MUST be taken up
cell by cell.
You can't mix individual cells and ranges of cells (as you can for instance
in the SUM function).
You have to specify each cell seperate.

So : =SmallAcc(A1,G1,G2,G3,H26,H40,etc) whereby in Cell A1 the number is
entered that decides which minimum (1 = lowest; 2 = second from below etc.)
you want to get back from the function)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top