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 ribbon > Evaluate Formula) for each.
1) =INDIRECT("C"&ROW(A1)) returns "Bob" (the value in C1)
2) =IF(INDIRECT("C"&ROW(A1))="","x","") returns "" but when evaluated using Evaluate Formula ROW returns {1} and the INDIRECT returns {#Value!}.
3) =IFERROR(IF(INDIRECT("C"&ROW(A1))="","x",""),"error") does not return "error" even thought INDIRECT evalutes to an error {#Value!}.
Now if you use the F9 function key to break down each formula (instead of Evaluate Formula) the INDIRECT function will return the same value/result from formula 1 within formula 2 and 3 . This explains why 3 doesn't truly evaluate to an error condition when wrapped in IFERROR but it's a real problem debugging formulas when the Evaluate Formula function is inaccurate.
I found the Evaluate Formula problem in trying to investigate the real issue which is this. With Excel 365 not upgraded to the new calc engine Formula 1 has no problem but formula 2 returns a #VALUE! error even though the ROW function only returns 1 row and thus the INDIRECT function is only evaluating 1 cell. I would think that if formula 1 returns a value that formula 2 should not produce an error.
Thanks in advance for any insight.
11 Replies
- Andy_Becker
Microsoft
Hi _K_O_,
Thanks for bringing this to our attention! We're investigating the issue and should have more to share soon.
Andy
Microsoft
- _K_O_Copper Contributor
Thanks Andy_Becker
- Detlef_LewinSilver Contributor
- _K_O_Copper Contributor
Thanks Detlef_Lewin
Wrapping INDIRECT with T( ) still returns the value as an array (i.e. { }) which is what is causing the problem inside the if for the version of Excel w/o the new calc engine.
- JKPieterseSilver ContributorExcel 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.
- JKPieterseSilver ContributorThere appears to be no attachment?
- _K_O_Copper Contributor
JKPieterse Sorry. Text updated. Formulas included within post as opposed to in an attached picture.