Excel: What determines evaluation order in a formu

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))

Answer 2

I think you were on course to investigate this using 'Formulas > Evaluate Formula'

The results are for typical math operations: functions are evaluated from the inside out.

Because =SUMPRODUCT(MIN(ABS($B1:$G1-$A1))) is not forced to evaluate as an array $B1:$G1 will return the value from that array from the same column from where the calling cell is located. I.e. if B2 = then $B1:$G1 will return B1, if A2= $B1:$G1 then it will try to return A1 but there is nothing to return so it gives you the #VALUE error.

Answer 3

Summarizing the comments by Brad and barry houdini (originally this):

The documentation says the ABS takes a number as its input, that MIN takes an arbitrary number of numbers, and SUMPRODUCT takes an arbitrary number or arrays. Seems like when the ABS is nested so deep it defaults to taking the number and can't figure out how to return an array.

So to counteract that we can use INDEX round ABS and get the correct result without "array entry" and without SUMPRODUCT, i.e. =MIN(INDEX(ABS($B1:$G1-$A1),0)).

This shows the right way of entering the formula, and it explains the cause of the error.

Answer 4

It looks like you are using the SUMPRODUCT function to make this formula work as an array formula and that Excel is not calculating your third formula as an array, giving you a #VALUE error when the formula is entered in column A. It did not give me this error in the columns B through G, but it also did not calculate as an array. Entering your formula as an array formula by pressing Shift+Ctrl+Enter after typing in your formula will fix this. You can also get the same result using a simpler formula:

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

Once this is entered as an array formula, you will be able to step through the evaluation and see it working correctly.

More info on arrays here: http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx