Min in array formula

[Jonno]

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,

 

[Bob Phillips]

Maybe

=IF(SUM((HoleX>=LoLim)*(HoleX<=HiLim)*Seal)=0,NA(),IF(0,"Multiple",
MIN(IF((HoleX>=LoLim)*(HoleX<=HiLim)*Seal<>0,(HoleX>=LoLim)*(HoleX<=HiLim)*Seal))))

BTW, what do you this this does

IF(0,"Multiple",...

 

[Jonno]

Thanks bob this works fine!!!

Sorry my excel assistant did this formula i think i will have to have words.

Many thanks,