Get min and max to ignore some returned results...

[spacely]

Hi,

I need to take the min or max of some returned if-then results, but I really need some of those if-then returns to be ignored so that the min or max result makes sense.

MIN(IF(hub!$F120=1,hub!G120,(10^10)),IF(bedplate!$F120=1,bedplate!G120,(10^10)))

I put in the (10^10) so that any competing and valid if-then returns will win. And then it makes sense. But if all if-then returns are (10^10), I really don't want the result to be 10^10. I could wrap the whole MIN in another if-then checking if the result is 10^10, and then replace it with something else, but then I have to double the size of the equation (the min is way longer than that).

Can i get an if-then that returns either a valid result, or a null that doesn't mess up min?

Thanks!

 

[L. Howard]

Look into the use of

NA() or ISNA()​

.

Might do what you want.

Howard

 

[Aladin Akyurek]

Looks like:

=MIN(IF(hub!$F120=1,IF(ISNUMBER(1/hub!G120),hub!G120,9.99E+307)),IF(bedplate!$F120=1,IF(ISNUMBER(1/bedplate!G120),bedplate!G120,9.99E+307)))
​

 

[spacely]

The NA() crashes min when some of the if-then's are returning valid values.

Aladin, I cannot use the value of hub!G120 as a test because it is, in fact, a value. Rather, what I'm after is, if hub!$F120=0, completely ignore the existence of hub!G120. Or rather return a benign thing that doesn't crash min, or register as a value within min.

 

[Marcelo Branco]

Maybe...

=IFERROR(SMALL(CHOOSE({1,2},IF(hub!$F120=1,hub!G120),IF(bedplate!$F120=1,bedplate!G120)),1),"")

M.

 

[spacely]

Seems the Min disappeared?
I'd need a similar for a Max version...

 

[Marcelo Branco]

SMALL(array,1) is similar to MIN(..), but it returns an error when the array has only FALSE values.

To get the max, in the formula above replace SMALL by LARGE

M.

 

[Jonmo1]

Assuming the value in F is either 1 or 0, And the values of G will not be negative.
Try this..

=MAX(hub!G120*hub!F120, bedplate!G120*bedplate!F120)

Use whichever cell address you put the max formula in for the Min formula below

=IF(MaxCell=hub!G120,bedplate!G120,hub!G120)