Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

You can avoid the array formula if we use some "helper" cells

Say in R99 we enter:

=IF(R2=0,"",R2) and copy across. Then we can simply use:

=MIN(R99:AA99)
--
Gary''s Student - gsnu200854


"art" wrote:

Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

An interesting alternative is:

=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)

But I don't know if this will pick up any speed performance.
--
Gary''s Student - gsnu200854


"art" wrote:

Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

={MIN(IF(R2:AA20,R2:AA2))}

One way, but the difference in calc speed is insignificant.

=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)

Also note that with the array formula if there are no numbers in the range
that meet the condition the result will be 0. With the SMALL version, you'll
get a #NUM! error.

--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is
an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range
which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

=IF(COUNTIF(R2:AA2,0)=COUNT(R2:AA2),0,SMALL(R2:AA2 ,COUNTIF(R2:AA2,0)+1))

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


"art" wrote:

Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

The SMALL formula does not seem to work if there are negative values present.
For example, if the data we

-5, -3, 0, 2, 4

the minimum should be -5
--
Gary''s Student - gsnu200854


"T. Valko" wrote:

={MIN(IF(R2:AA20,R2:AA2))}

One way, but the difference in calc speed is insignificant.

=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)

Also note that with the array formula if there are no numbers in the range
that meet the condition the result will be 0. With the SMALL version, you'll
get a #NUM! error.

--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is
an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range
which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

hi, all !

- FWIW, OP never mentioned negative values
actuall formula in use: ={MIN(IF(R2:AA20,R2:AA2))}

- for negative values included, Tony's approach could it be:
=SMALL(R2:AA2,COUNTIF(R2:AA2,"=0")+1)

- the following formula does not require to be array-entered (CSE):
=sumproduct(min((r2:aa2<=0)*--"9e307"+r2:aa2))
however it "works" on array arrangements and also...
it's calc time consumption is a little big than a CSE formula

hth,
hector.

Gary''s Student wrote in message ...
The SMALL formula does not seem to work if there are negative values present.
For example, if the data we
-5, -3, 0, 2, 4
the minimum should be -5

T. Valko wrote:
={MIN(IF(R2:AA20,R2:AA2))}
One way, but the difference in calc speed is insignificant.
=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)
Also note that with the array formula if there are no numbers in the range
that meet the condition the result will be 0. With the SMALL version, you'll get a #NUM! error.

art wrote in message ...
I have a formula to calculate the Min value of a range that has includes zero values
which the min formula ignors the zero valus.
However, it is an array function which slows down my spreadsheet
since I have this arrayformula on a long list.
Is there a way to calculate the min of a range which
has also some zero values without an array function?
The formula I use now is as follows:
={MIN(IF(R2:AA20,R2:AA2))}

So what it seems, is that I can't really escape my speed problem. Whatever
I'll do, the alternative would not have any significant speed increase.

Thanks for your input.

Art.

"Gary''s Student" wrote:

You can avoid the array formula if we use some "helper" cells

Say in R99 we enter:

=IF(R2=0,"",R2) and copy across. Then we can simply use:

=MIN(R99:AA99)
--
Gary''s Student - gsnu200854


"art" wrote:

Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

The only way to be sure is to try it!

--
Biff
Microsoft Excel MVP


"art" wrote in message
...
So what it seems, is that I can't really escape my speed problem. Whatever
I'll do, the alternative would not have any significant speed increase.

Thanks for your input.

Art.

"Gary''s Student" wrote:

You can avoid the array formula if we use some "helper" cells

Say in R99 we enter:

=IF(R2=0,"",R2) and copy across. Then we can simply use:

=MIN(R99:AA99)
--
Gary''s Student - gsnu200854


"art" wrote:

Hello:

I have a formula to calculate the Min value of a range that has
includes
zero values, which the min formula ignors the zero valus. However, it
is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range
which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

hi, Gary''s !

An interesting alternative is:
=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)
But I don't know if this will pick up any speed performance...

(just *curious*)... do you have a "time travel machine" ?

- the first post from you that I "saw" (in my OE) was it the one ~20 minutes *after* Tony's post...
where you state that the SMALL function doesn't work when negative values are present

- and *suddenly* (in my OE) *appears* a second post (from you) but ~3 minutes *before* Tony's post...
and your proposal is (exactly) the same Tony's formula, the one that you *after* question for negative values (???)

as I said... (just *curious*) :D

regards,
hector.

Hi,

You can use the Database functions. Suppose the range of numbers is in
A5:A35000. In cell A4, type Nos. Now in cell B4, type Nos and in cell B5,
type 0. in cell E14, type
=DMIN(A4:A35000,B4,B4:B5)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"art" wrote in message
...
Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is
an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range
which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

Time travel is very reliable, but only in the forward direction!

Benadryl won yesterday and I got the formula wrong. Clearly:

If MIN() does not return zero then
use MIN()
else
use the SMALL() formula
endif

=IF(MIN(R2:AA2)<0,MIN(R2:AA2),SMALL(R2:AA2,COUNTI F(R2:AA2,0)+1))

This gets the correct min with any combination of positive and negatives and
specifically excludes zero.
--
Gary''s Student - gsnu200854


"Héctor Miguel" wrote:

hi, Gary''s !

An interesting alternative is:
=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)
But I don't know if this will pick up any speed performance...

(just *curious*)... do you have a "time travel machine" ?

- the first post from you that I "saw" (in my OE) was it the one ~20 minutes *after* Tony's post...
where you state that the SMALL function doesn't work when negative values are present

- and *suddenly* (in my OE) *appears* a second post (from you) but ~3 minutes *before* Tony's post...
and your proposal is (exactly) the same Tony's formula, the one that you *after* question for negative values (???)

as I said... (just *curious*) :D

regards,
hector.

Min excluding zero values, with postive and negative values present.

1] An array formula :

{=MIN(--TEXT(R2:AA2,"[=]9.99E+307"))}

2] An non-array formula :

=SMALL(R2:AA2,IF(COUNTIF(R2:AA2,"<0"),1,COUNTIF(R2 :AA2,0)+1))

Regards
Bosco


"art" wrote:

Hello:

I have a formula to calculate the Min value of a range that has includes
zero values, which the min formula ignors the zero valus. However, it is an
array function which slows down my spreadsheet, since I have this array
formula on a long list. Is there a way to calculate the min of a range which
has also some zero values without an array function?

The formula I use now is as follows:

={MIN(IF(R2:AA20,R2:AA2))}

Thanks.

Art

Just so there's no confusion...

The OP posted this formula:

={MIN(IF(R2:AA20,R2:AA2))}

Since they're testing for 0 that tells me there are no -ve numbers in the
range.

--
Biff
Microsoft Excel MVP


"Héctor Miguel" wrote in message
...
hi, all !

- FWIW, OP never mentioned negative values
actuall formula in use: ={MIN(IF(R2:AA20,R2:AA2))}

- for negative values included, Tony's approach could it be:
=SMALL(R2:AA2,COUNTIF(R2:AA2,"=0")+1)

- the following formula does not require to be array-entered (CSE):
=sumproduct(min((r2:aa2<=0)*--"9e307"+r2:aa2))
however it "works" on array arrangements and also...
it's calc time consumption is a little big than a CSE formula

hth,
hector.

Gary''s Student wrote in message ...
The SMALL formula does not seem to work if there are negative values
present.
For example, if the data we
-5, -3, 0, 2, 4
the minimum should be -5

T. Valko wrote:
={MIN(IF(R2:AA20,R2:AA2))}
One way, but the difference in calc speed is insignificant.
=SMALL(R2:AA2,COUNTIF(R2:AA2,0)+1)
Also note that with the array formula if there are no numbers in the
range
that meet the condition the result will be 0. With the SMALL version,
you'll get a #NUM! error.

art wrote in message ...
I have a formula to calculate the Min value of a range that has
includes zero values
which the min formula ignors the zero valus.
However, it is an array function which slows down my spreadsheet
since I have this arrayformula on a long list.
Is there a way to calculate the min of a range which
has also some zero values without an array function?
The formula I use now is as follows:
={MIN(IF(R2:AA20,R2:AA2))}