PDA

View Full Version : Solved: Min formula


Barryj
05-14-2006, 03:56 PM
I have been trying to get a min formula working without success, I have tried =MIN(X14:X149,1), as the lowest number will either be a blank cell or 0, but I need the minimum number greater than 0 the excel help file said use the above formula if the lowest number is 0 but it still returns 0 not 1.

Any ideas?
Daxton A.
05-14-2006, 04:57 PM
I have been trying to get a min formula working without success, I have tried =MIN(X14:X149,1), as the lowest number will either be a blank cell or 0, but I need the minimum number greater than 0 the excel help file said use the above formula if the lowest number is 0 but it still returns 0 not 1.

Any ideas?

Is this what would work?

=IF(MIN(X14:X149) = 0, 1, MIN(X14:X149))
Daxton A.
05-14-2006, 04:58 PM
Is this what would work?

=IF(MIN(B1:B5) = 0, 1, MIN(B1:B5))
Barryj
05-14-2006, 05:50 PM
Tried both of your solutions and they showed either 1 if there was a 0 in the list or 1 if the lowest number was 3, what I am looking for is the lowest number greater than 0 in the list, I don't want it to count 0 as the lowest number.
mdmackillop
05-15-2006, 02:45 PM
How about using a UDF?

Function MyMin(Data As Range)
Dim Mn As Double, d
Mn = 1000
For Each d In Data
If d < Mn And d > 0 Then Mn = d
Next
MyMin = Mn
End Function
lenze
05-15-2006, 03:07 PM
Use this Formula
=MIN(IF(X14:X149>0,X14:X149))

Must be confirmed with CTRL+SHIFT+ENTER

lenze
Zack Barresse
05-15-2006, 03:30 PM
Or a non-array entered ..

=SUMPRODUCT(((X15:X150>0)*(MIN(X15:X150)=X15:X150)),X15:X150)

Although the array entered would probably calc faster in larger data sets.
Shazam
05-15-2006, 04:38 PM
How about using:

non-array:

=SMALL(X14:X149,COUNTIF(X14:X149,0)+1)


Hit enter.
Barryj
05-15-2006, 04:49 PM
Still no luck with these formulas, have included a sample sheet showing 2 of the formulas and the results.
Shazam
05-15-2006, 04:58 PM
Hi BarryJ,


Did you try my formula?
Barryj
05-15-2006, 05:03 PM
Thankyou Shazam!, works perfect.
lenze
05-15-2006, 07:14 PM
Still no luck with these formulas, have included a sample sheet showing 2 of the formulas and the results.
Hi Barry: I'm glad you got an answer that works for you. The reason, however, you received a wrong answer with my formula is because it is an array (CSE) formula, and must be entered with CTRL+SHIFT+ENTER, as noted in my post. Open the sample workbook you posted and select the cell with the array formula. Click at the end of the formula in the formula bar and then enter CTRL+SHIFT+ENTER. You will get the correct result.

lenze