Forum Discussion
Unexpected result from new calc engine using IF, INDIRECT, ROW
So I got more unexpected results with the new calculation engine. See formulas 1, 2, and 3 below with commentary. You can get the same results as I get if you use the Evaluate Formula (Data ribb...
Excel appears not to understand that INDIRECT (in this case) is returning a single value. If you add an @ in front of the INDIRECT the results are as expected. I think this is a bug of the function evaluator.
Thanks and I tend to agree I think there is a bug in the formula evaluator.
I'm not certain the problem is solely with the INDIRECT function. In formula 1 ROW returns a single value (as would be expected) and thus INDIRECT has no problem. In formula 2 (when inside an IF function) ROW returns an array which then causes INDIRECT to return an array (even though there is only 1 cell reference inside ROW). Further testing I removed the INDIRECT so just =ROW(A28) which returns 1 value (not an array) and =IF(ROW(A28),1,"") in which case ROW also only returns 1 value (not an array). So the problem is only when ROW is inside an INDIRECT and INDIRECT is inside an IF.
- I have notified the Excel team about this.
- There should be an update soon that fixes this.