Understanding the results using array arguments in a nested If() function

[CJ]

Hello,

I have been using some advanced formulas that I have picked up on this
board and I am trying to understand better how they work.

For example:

Match(Min(If(F2:F6-F8>=0,F2:F6,FALSE)),If(F2:F6-F8>=0,F2:F6,FALSE),0)

The part that I am vague on is what gets returned in the value_if_true
arguments here in the two If statements. It's not the whole array of
F2:F6 getting returned here but only those elements of it that
evaluate to true in the logical_test or 1st argument of the If
function:

F2-F8>=0 = TRUE
F3-F8>=0 = FALSE
F4-F8>=0 = FALSE
F5-F8>=0 = TRUE
F6-F8>=0 = TRUE

Array returned would be {F2,F5,F6}, correct?

Thanks

 

[Jerry W. Lewis]

Your array formula can be simplified to
=Match(Min(If(F2:F6-F8>=0,F2:F6)),F2:F6,0)
or even
=Match(Min(If(F2:F6>=F8,F2:F6)),F2:F6,0)

As for the explantion,
If(F2:F6-F8>=0,F2:F6)
returns an array of 5 values, containing the numerical values from F2:F6
that are at least as big as F8. Logical FALSE values are returned in
place of values from F2:F8 that are either non-numeric, or are less than
F8. Thus
Min(...)
returns the smallest numeric value from F2:F8 that is at least as large
as F8. So that
Match(...)
returns relative position in F2:F6 where that value can be found.

Jerry

 

[CJ]

Thanks, Jerry. I was close but your explanation makes sense. Thanks
also for offering a simplification of the formula.

- CJ

 

[Jerry W. Lewis]

You're welcome.

Jerry