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

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

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