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

  • Hi _K_O_,

     

    Thanks for bringing this to our attention!  We're investigating the issue and should have more to share soon.

     

    Andy

    Microsoft

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    _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

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

      JKPieterse Sorry.  Text updated.  Formulas included within post as opposed to in an attached picture.

Resources