Minimum of all values > 0

[Latexman]

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

 

[Denial]

Assuming your range is D3:E35, use the formula[tt]
=MIN(IF(D3:E35=0,9999999,D3:E35))
[/tt]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æ.

 

[johnwm]

You could also use VBA:

Private Sub Worksheet_Calculate()
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

This assumes your test values are in A1:A40 and puts the result in D3

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[Latexman]

Arrays are slick!

Looks like
=MIN(IF((C4:C14)>0,(C4:C14),"")) [Ctrl-Shift-Enter]
works too.

Good luck,
Latexman