Menu
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?
504
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel performs multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
Operator precedence If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.
Solution(By Examveda Team)None of above is correct order of precedence in formula calculation.
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))
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
