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