Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min in array formula
Hi,
{=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA( ),IF(0,"Multiple",MAX((HoleX=LoLim)*(HoleX<=HiLim )*Seal)))} The formula above is part of a spread sheet which im creating to select a certain seal for certains holes. The formula above works fine if its choosing the larger seal. (MAX) But if i want the formula to choose the smaller seal then i thought this would work. {=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA( ),IF(0,"Multiple",MIN((HoleX=LoLim)*(HoleX<=HiLim )*Seal)))} All i have done is changed the MAX to MIN. this now only results in 0. I understand why it results in zero. But i need to know how to make it select the smallest value other then zero. Many Thanks, -- Jonno |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min in array formula
Maybe
=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA() ,IF(0,"Multiple", MIN(IF((HoleX=LoLim)*(HoleX<=HiLim)*Seal<0,(Hole X=LoLim)*(HoleX<=HiLim)*Seal)))) BTW, what do you this this does IF(0,"Multiple",... -- __________________________________ HTH Bob "Jonno" wrote in message ... Hi, {=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA( ),IF(0,"Multiple",MAX((HoleX=LoLim)*(HoleX<=HiLim )*Seal)))} The formula above is part of a spread sheet which im creating to select a certain seal for certains holes. The formula above works fine if its choosing the larger seal. (MAX) But if i want the formula to choose the smaller seal then i thought this would work. {=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA( ),IF(0,"Multiple",MIN((HoleX=LoLim)*(HoleX<=HiLim )*Seal)))} All i have done is changed the MAX to MIN. this now only results in 0. I understand why it results in zero. But i need to know how to make it select the smallest value other then zero. Many Thanks, -- Jonno |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min in array formula
Thanks bob this works fine!!!
Sorry my excel assistant did this formula i think i will have to have words. Many thanks, -- Jonno "Bob Phillips" wrote: Maybe =IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA() ,IF(0,"Multiple", MIN(IF((HoleX=LoLim)*(HoleX<=HiLim)*Seal<0,(Hole X=LoLim)*(HoleX<=HiLim)*Seal)))) BTW, what do you this this does IF(0,"Multiple",... -- __________________________________ HTH Bob "Jonno" wrote in message ... Hi, {=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA( ),IF(0,"Multiple",MAX((HoleX=LoLim)*(HoleX<=HiLim )*Seal)))} The formula above is part of a spread sheet which im creating to select a certain seal for certains holes. The formula above works fine if its choosing the larger seal. (MAX) But if i want the formula to choose the smaller seal then i thought this would work. {=IF(SUM((HoleX=LoLim)*(HoleX<=HiLim)*Seal)=0,NA( ),IF(0,"Multiple",MIN((HoleX=LoLim)*(HoleX<=HiLim )*Seal)))} All i have done is changed the MAX to MIN. this now only results in 0. I understand why it results in zero. But i need to know how to make it select the smallest value other then zero. Many Thanks, -- Jonno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Help | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array formula | Excel Discussion (Misc queries) | |||
Array formula help please | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |