Minimum of all values > 0
Minimum of all values > 0
(OP)
I have a range of values. Some are 0 and the rest are > 0. I want the minimum of all the values > 0 using only one cell for the cell math. How?
Good luck,
Latexman





RE: Minimum of all values > 0
=MIN(IF(D3:E35=0,9999999,D3:E35))
but enter it as an "array formula".
Depending on the range of values you anticipate and how they have been generated, you may want to fine-tune this by:
(a) using some other default "large" value than 9999999;
(b) changing the "=0" test to "<0.001" or some other default "small" value.
Array formulæ are entered by holding down the <Ctrl> and <Shift> keys when you hit the <Enter> key. They will then display enclosed in {braces}.
I'm sure other posters will be making other, equally valid suggestions. Even if you end up going with some other approach, it is worth investing time coming to grips with the potential of array formulæ.
RE: Minimum of all values > 0
CODE
temp = 10000000
For a = 1 To 40
current = Cells(a, 1)
If current > 0 Then
temp = IIf(temp < current, temp, current)
End If
Next
Cells(3, 4).Value = temp
End Sub
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Minimum of all values > 0
Looks like
=MIN(IF((C4:C14)>0,(C4:C14),"")) [Ctrl-Shift-Enter]
works too.
Good luck,
Latexman