Min excluding zero values, without an array fomrula

[art]

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:AA2>0,R2:AA2))}

Thanks.

Art

 

[Gary''s Student]

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]

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.

 

[T. Valko]

={MIN(IF(R2:AA2>0,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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Gary''s Student]

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

-5, -3, 0, 2, 4

the minimum should be -5

 

[Héctor Miguel]

hi, all !

- FWIW, OP never mentioned negative values
actuall formula in use: ={MIN(IF(R2:AA2>0,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.

 

[art]

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.

 

[T. Valko]

The only way to be sure is to try it!

 

[Héctor Miguel]

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*)

regards,
hector.

 

[Ashish Mathur]

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

 

[Gary''s Student]

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,COUNTIF(R2:AA2,0)+1))

This gets the correct min with any combination of positive and negatives and
specifically excludes zero.

 

[bosco_yip]

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