Unexpected result from new calc engine using IF, INDIRECT, ROW

[Kevin Osborn]

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.

[#2]

There appears to be no attachment?

[#3]

@Jan Karel Pieterse Sorry.  Text updated.  Formulas included within post as opposed to in an attached picture.

[#4]

@Kevin Osborn 

Change formula #2 into this and evaluate again:

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

[#5]

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.

[#6]

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.

[#7]

@Jan Karel Pieterse 

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.

[#8]

I have notified the Excel team about this.

[#9]

Thanks @Jan Karel Pieterse 

I also reported it via the :( within Excel.

[#10]

There should be an update soon that fixes this.

[#11]

Hi @Kevin Osborn,

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

Andy

Microsoft

[#12]

Thanks @Andy_Becker