nested - Excel: What determines evaluation order in a formula?

Question

I have a worksheet with the following contents in A1:G1

7  8  4  2  9  11  10

Formula

=SUMPRODUCT(MIN($B1:$G1-$A1)) (1)

evaluates to -5,

=SUMPRODUCT(ABS($B1:$G1-$A1)) (2)

evaluates to 18. But

=SUMPRODUCT(MIN(ABS($B1:$G1-$A1))) (3)

gives #VALUE!.

To try to understand the issue, I use Formula Auditing -> Evaluate Formula. In the formulas that work (1 and 2), $A1 is evaluated (underlined) first. In the formula that doesn't work (3), $B1:$G1 is evaluated (underlined) first.

What is the reason for the error, and the different behavior among formulas?

Answer 1

As per my comment, to get the smallest difference between A1 and B1:G1 without using an "array entered" formula you can use INDEX to do what you were trying to do with SUMPRODUCT, i.e.

=MIN(INDEX(ABS($B1:$G1-$A1),0))