Min excluding zero values, without an array fomrula
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))}
|