PS: Personally, I don't feel that any of this is worth the
trouble just to avoid the straight-forward array formula

I agree completely. It was just an academic exercise.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))

I think this is the most reliable expressions of this ilk; "reliable"
because I believe it works for all values in A1:A7 less then 1E100, even
if MIN(A1:A7) is 2^53 or more less than 1E100.

However, I believe that expression fails if MIN(A1:A7) is greater than
1E100. Moreover, it can be simplified. I believe the following is more
reliable:

=min(index(A1:A7+2*max(A1:A7)*(A1:A7=0),,1))

I believe that works as long as MAX(A1:A7) is less than 2^1023.

We could extend the allowed numerical range for A1:A7 somewhat by
replacing "2*" with a smaller factor larger than 1; the smallest constant
factor is 1.00000000000001. But it does not seem to be worth the trouble.

PS: Personally, I don't feel that any of this is worth the trouble just
to avoid the straight-forward array formula, as much as I dislike array
formulas in general.


----- original message -----

"T. Valko" wrote in message
...
If you don't want an array formula

Assuming there will always be at least 1 non-zero cell and there's no
text in the range:

=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.