|
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! |
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! |
minimum from various columns without zeroes
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! |
minimum from various columns without zeroes
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 |
minimum from various columns without zeroes
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! |
minimum from various columns without zeroes
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 |
| All times are GMT +1. The time now is 04:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com