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: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!

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: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!

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" wrote in message
...
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: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!

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

What if there are 2 values with zero?

--
Regards,

Peo Sjoblom

(No private emails please)


"Cutter" wrote in
message ...

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