minimum from various columns without zeroes
If there is some pattern or a range it can be done by
=MIN(IF(F16:Z160,F16:Z16))
entered with ctrl + shift & enter
or with a pattern like every 5th cell like with F16, K16 and P16
=MIN(IF((MOD(COLUMN(F16:Z16),5)=1)*(F16:Z160),F16 :Z16))
entered with ctrl + shift & enter
however since you jump to Z16 from K16 one cannot use that unless you missed
out U16?
it's probably easier to check each cell since there are only 4 otherwise
this might work
=INDEX(SMALL((F16,K16,P16,Z16),ROW(INDIRECT("1:"&C OUNT(F16,K16,P16,Z16)))),MATCH(TRUE,SMALL((F16,K16 ,P16,Z16),ROW(INDIRECT("1:"&COUNT(F16,K16,P16,Z16) )))0,0))
entered with ctrl + shift & enter
or
=MIN(IF(LARGE((F16,K16,P16,Z16),ROW(INDIRECT("1:"& COUNT(F16,K16,P16,Z16))))0,LARGE((F16,K16,P16,Z16 ),ROW(INDIRECT("1:"&COUNT(F16,K16,P16,Z16))))))
entered the same way
--
Regards,
Peo Sjoblom
(No private emails please)
"joie" wrote in message
...
Hi! Just a question:
I was computing for the minimum, using =min(f16,k16,p16,z16) but the
problem
is I don't want the minimum to equal to zero (I want to see the lowest
positive number from these columns instead of zero).
Is there a way not to include zeroes from these columns in looking for the
minimum?
Thanks a lot!
|