MIN function, ignore blanks and errors, not a continuous range...

Hi,

I want to take the MIN of several multiplied cell contents that are not in a range, and have it ignore results that are not numeric. So:

=MIN(E138*F138,G138*I138,J138*L138,M138*P138)

...is returning 0 because G138 is empty. If I try to put a character in that cell, the result is #VALUE!. Most Array solutions I see out there require a continuous range to work. Maybe a CountIf can work? I'm just not sure what approach to use here.

Any help appreciated. Thanks in advance

Thank you very much. And for the MAX side of it, I'll use -10^10. My data will never break those bounds. Thanks for being so quick!

Actually, the MAX equation wants -(10^10):

=MAX(IF(E155*F155=0,-(10^10),E155*F155),IF(G155*I155=0,-(10^10),G155*I155),IF(J155*L155=0,-(10^10),J155*L155),IF(M155*P155=0,-(10^10),M155*P155))

Thanks again.

Originally Posted by spacely

Thank you very much. And for the MAX side of it, I'll use -10^10. My data will never break those bounds. Thanks for being so quick!

You wouldn't need to use that for MAX, the only time that would return 0 is if every pair of cells contained a zero value, in which case, 0 would be the correct result.

Similarly, bebo's formula will incorrectly return 10^10 if every pair of cells returns 0, when 0 would be the correct result.

This will return the correct result, and still return a genuine zero when appropriate.

=MIN(IFERROR(1/(1/CHOOSE({1,2,3,4},E138*F138,G138*I138,J138*L138,M138*P138)),""))

Note that this is an array formula which must be confirmed by pressing Shift Ctrl and Enter together, not just enter.

Originally Posted by bebo021999

Assuming that all values are negative, except 1 cell =0, then MAX = 0. To avoid this, assign 0 to be -10^10 is reasonable.

But based on the original problem with MIN returning zero it is safe to assume that the values are positive.

Originally Posted by jason.b75

You wouldn't need to use that for MAX, the only time that would return 0 is if every pair of cells contained a zero value, in which case, 0 would be the correct result.

Similarly, bebo's formula will incorrectly return 10^10 if every pair of cells returns 0, when 0 would be the correct result.

This will return the correct result, and still return a genuine zero when appropriate.

=MIN(IFERROR(1/(1/CHOOSE({1,2,3,4},E138*F138,G138*I138,J138*L138,M138*P138)),""))

Note that this is an array formula which must be confirmed by pressing Shift Ctrl and Enter together, not just enter.

Hi Jason,

I wanted to revisit this solution using IFERROR and CHOOSE, this time for use in an IF-THEN situation. So, I have:

=IF(DA9=N9*P9,N9,IF(DA9=Q9*S9,Q9,IF(DA9=T9*V9,T9,IF(DA9=W9*Y9,W9,"?"))))

...where W9 contains a formula that puts a blank in the cell, and so messes up the IF evaluation. This is a shortened equations, where there are many such "blanks". I see how the MIN is working, but not how to do an "ISEQUAL" kind of thing, so maybe we're stuck with IF?

Hope you're still there, and thanks!
Dave

I found a solution. I just checked each multiplication for success before taking the IF:

=IF(DA9=ISERROR(N9*P9,10^10),N9, ... etc

It will never equal 10^10, so safe.