minimum from various columns without zeroes

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!

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)


"joie" <[email protected]> wrote in message
news:[email protected]...
> 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!

How about:

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

Hey! Thanks!

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

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

"Peo Sjoblom" wrote:

> 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)
>
>
> "joie" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>

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

--
Regards,

Peo Sjoblom

(No private emails please)


"joie" <[email protected]> wrote in message
news:[email protected]...
> Hey! Thanks!
>
> Actually, I just missed out U16... Sorry, my mistake...
>
> But thanks! Thanks! Thanks! The formula worked like magic!
>
> "Peo Sjoblom" wrote:
>
>> 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)
>>
>>
>> "joie" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!
>>
>>

What if there are 2 values with zero?

--
Regards,

Peo Sjoblom

(No private emails please)


"Cutter" <[email protected]> wrote in
message news:[email protected]...
>
> How about:
>
> =IF(MIN(F16,K16,P16,Z16)=0,SMALL((F16,K16,P16,Z16),2),MIN(F16,K16,P16,Z16))
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile:
> http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=488953
>