minimum from various columns without zeroes

[Guest]

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!

 

[Peo Sjoblom]

If there is some pattern or a range it can be done by

=MIN(IF(F16:Z16>0,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:Z16>0),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:"&COUNT(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)

 

[Guest]

Hey! Thanks!

Actually, I just missed out U16... Sorry, my mistake...

But thanks! Thanks! Thanks! The formula worked like magic!

 

[Cutter]

How about:

=IF(MIN(F16,K16,P16,Z16)=0,SMALL((F16,K16,P16,Z16),2),MIN(F16,K16,P16,Z16)

 

[Peo Sjoblom]

I must admit I thought so since the 3 first had a pattern
thanks for the feedback

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Peo Sjoblom]

What if there are 2 values with zero?

--
Regards,

Peo Sjoblom

(No private emails please)