Here's an array formula that will work for you:
=SMALL(IF(MOD(ROW(A1:A10),2)=0,A1:A10,""),1)
As this is an "Array" formula, after coping it into the cell you will
need to press Ctrl+Shift+Enter instead of just Enter

Charles Chickering

RobcPettit wrote:
Hi, in ranges f8:f15 i have
3.98
1463
3.98
1567
3.98
1572
4
2
What id like to do is look for the smallest value in every other cells.
So the first cell = 3.98, next = 3.98 etc. At the moment I do,
odds(1) = Range("f8")
odds(2) = Range("f10")
odds(3) = Range("f12")
odds(4) = Range("f14")
Count = 1000
For i = 1 To 4
If odds(i) < 0 Then
If odds(i) < Count Then
Count = odds(i)
hand = i
Range("a40") = hand
Range("b40") = Count
End If
End If


the hand value is which one of the odds() holds the value, to be used
later. This works ok, but I was wondering if there a more efficient
way. I though maybe a formula in the worksheet itself. Any ideas?
Regards Robert