Forum Discussion

_K_O_'s avatar
_K_O_
Copper Contributor
Mar 31, 2020

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
Formulas and Functions
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Mar 31, 2020

_K_O_ 

Change formula #2 into this and evaluate again:

=IF(T(INDIRECT("C"&ROW(A1)))="","x","")

 

  • _K_O_'s avatar
    _K_O_
    Copper Contributor
    Mar 31, 2020

    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.

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Mar 31, 2020
      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.
      • _K_O_'s avatar
        _K_O_
        Copper Contributor
        Mar 31, 2020

        JKPieterse 

         

        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.

Resources