Divide by the minimum non-zero number in a range of cells

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.

=MIN(IF(A1:A10;A1:A10))

this is an array-formula so CTRL+SHIFT+ENTER instead of simply
entering it



On 13 Cze, 11:13, 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.

Suppose you have the numbers in Column A use the below formula to return the
minimum non zero number. Hope you dont have negative numbers..

=SMALL(A:A,COUNTIF(A:A,0)+1)

If this post helps click Yes
---------------
Jacob Skaria


"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.

Hello Opus,

This works, assuming your data is in cells A1 to G1.

=MIN(IF($A$1:$G$1=0,MAX($A$1:$G$1),$A$1:$G$1))

You have to submit it as an array formula (type the formula then press
Ctrl+Shift+Enter).

What it does is look at all the values individually, and any time it finds a
zero it thinks about it as the biggest number in the range instead (thereby
excluding it from the MIN).
--
If you found this post helpful, please click "Yes".
Allllen


"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.

This works, assuming your data is in cells A1 to G1.

=MIN(IF($A$1:$G$1=0,MAX($A$1:$G$1),$A$1:$G$1))

You have to submit it as an array formula (type the formula then press
Ctrl+Shift+Enter).

What it does is look at all the values individually, and any time it finds a
zero it thinks about it as the biggest number in the range instead (thereby
excluding it from the MIN).
--
If you found this post helpful, please click "Yes".
Allllen


"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.

=MIN(IF(A1:A100,A1:A10))

NOTE:

This is an array formula that must be entered with CNTRL-SHFT-ENTER instead
of just the ENTER key.
--
Gary''s Student - gsnu200856


"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.

=MIN(IF(a1:g1=0,max(a1:g1),a1:g1)
array formula: press ctrl+shift and enter to submit it
--
If you found this post helpful, please click "Yes".
Allllen


"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.

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.

Shane Devenshire;380163 Wrote:
If you don't want an array formula try this

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

Surely that won't always return the correct result? If all cells A1:A7
contain the number 1 then that formula will give you 7


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106326

"Shane Devenshire" wrote:
If you don't want an array formula try this
=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

Doesn't work for me. Test with A1:A7 = {0,3,3,2,3,3,0}. Returns zero. It
is easy to see why: MIN(A1:A7) is a constant, namely the smallest in the
range. Since that would be zero, the very case that the OP wants to
exclude, the SUMPRODUCT should always be zero.


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

"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.