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?

I love this question. Looking it up I found this not perfectly satisfying solution - but it might come handy: http://office.microsoft.com/en-001/excel-help/overview-of-formulas-HP010081865.aspx — , Nov 19 '13 at 18:05
For the third formula, I can replicate when the formula is in column A, but if I fill right, I see different results in each cell, which is very odd, since the formula doesn't change. — mr.Reband, Nov 19 '13 at 18:11
@mr.Reband Nice find! Actually, I had originally entered my formula in O1. I will dig here. Still, I find it intriguing that: 1) the order of evaluation is different, 2) filling similarly formula (1) to the right gives a constant result, as opposed to formula (3). — sancho.s ReinstateMonicaCellio, Nov 19 '13 at 18:29
@Mark : I did not find a hint for a solution in your link. Were you referring to entering the formula as an array (as in the answer by John)? — sancho.s ReinstateMonicaCellio, Nov 19 '13 at 18:39
@sancho.s Yup, that's why I added a comment and mentioned what I found is not a solution but may give a better understand and can pop new ideas (or not) :) — , Nov 20 '13 at 07:30
I actually get `1` rather than `#VALUE` for the third formula (Windows 7, Excel 2010) — airstrike, Jul 17 '15 at 17:54

score 4 · Accepted Answer · answered Nov 19 '13 at 19:07

4

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

answered Nov 19 '13 at 19:07

barry houdini

45,615
8
63
81

score 3 · Answer 2 · answered Nov 19 '13 at 18:12

3

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

answered Nov 19 '13 at 18:12

John

540
2
15

1

Thanks for the pointer. I know that entering it as an array formula it works, but I meant to avoid precisely that. This is the reason for introducing `SUMPRODUCT`, and I do not understand why it works only in some cases. The question was aimed specifically at understanding this point, not at alternative ways of arriving at the intended result. – sancho.s ReinstateMonicaCellio Nov 19 '13 at 18:21
Nice first answer! +1 – Doug Glancy Nov 19 '13 at 18:21
@sancho.s, if that was specifically your point, it was worth mentioning in your question :). – Doug Glancy Nov 19 '13 at 18:23
1

+1, excellent answer - SUMPRODUCT doesn't always magically convert formulas that require array entry into ones that don't, I assume that ABS needs CSE whatever - TRANSPOSE falls into the same category – barry houdini Nov 19 '13 at 18:41
1

@sancho.s, Your issue is not the evaluation order, but the way Excel is evaluating (array vs. regular function). The solution is to force Excel to evaluate as an array instead of letting it decide how to evaluate by nesting non-array functions inside of an array function (SUMPRODUCT is an array function). It is interesting though how Excel flips the order of evaluations when evaluating as an array vs a regular function. I had never noticed that. – John Nov 19 '13 at 18:41
@John : But then why the different behavior when used with only one of `MIN` or `ABS`, as compared to both? Understanding this may lead to entering the formula suitably, and avoiding an array formula. Would you think that is not possible? – sancho.s ReinstateMonicaCellio Nov 19 '13 at 18:41
@barryhoudini : This sort of frames the cause to the kind of input `SUMPRODUCT` takes, but then why the different behavior when used with only one of `MIN` or `ABS`, as compared to both? I still find it strange, as they are nested inside `SUMPRODUCT`. PS: I do not know what CSE means. – sancho.s ReinstateMonicaCellio Nov 19 '13 at 18:46
2

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 – Brad Nov 19 '13 at 18:46
Sorry CSE = CTRL+SHIFT+ENTER – barry houdini Nov 19 '13 at 18:47
Can we tag this as part of the Magic of Excel? – sancho.s ReinstateMonicaCellio Nov 19 '13 at 18:49
1

Good point Brad, 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))` – barry houdini Nov 19 '13 at 18:51
@barryhoudini : Awesome! This look very much the answer. I would post it as such. – sancho.s ReinstateMonicaCellio Nov 19 '13 at 18:57

score 3 · Answer 3 · edited May 23 '17 at 12:24

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.

score 2 · Answer 4 · answered Nov 19 '13 at 18:22

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.

answered Nov 19 '13 at 18:22

Brad

11,934
4
45
73

I admit to not fully understanding your answer (I will think about it). In the meantime, do you think it explains the different behavior when used with only one of `MIN` or `ABS`, as compared to both? – sancho.s ReinstateMonicaCellio Nov 19 '13 at 18:33

Excel: What determines evaluation order in a formula?

4 Answers4

Linked