Nested IF Evaluates zero at runtime but not in excel

Answered (Verified) This post has 1 verified answer | 6 Replies | 2 Followers Thread's RSS feed.

Jesse
Points 65
Replied On: Wed, Oct 11 2017 3:45 PM Reply

We have a multi-sheet workbook used for calculating population growth estimates. Sheet A has a semi-complicated formula which is basically a nested IF(OR()) function with 3 if blocks. The formula references cells in another sheet which are formulas to other columns in a sheet.  The value in excel appears to work as expected. But after loading into Excel Worksheet (Infragistics.Documents.Excel.16.2) in memory all the values appear to calculate correctly except the ones referencing across worksheet. 

Example: We have a detail sheet and a summary sheet. Summary sheet has the following Formula: =IF(OR(Detail!$A$28, Detail!$A$29), Detail!E$16, IF(Detail!$A$30, Detail!E$17, IF(Detail!$A$31, Detail!E$18, Detail!E$17))) where Detail A28 - A31 are string values like TRUE or FALSE that evaluate to boolean. The other refernced columns E16, E17, E18 etc are functions adding other columns in the Detail sheet which I can inspect and all run correctly. But the function on the Summary Sheet that references the Detail Sheet just return a zero, which isn't possible if it looks at ANY of the other fields. 

This problem is new after upgrading from version 9.x to 16.x and I'm sure there have been a lot of changes since then. Any pointers? I am unable to find ANYTHING referencing this sort of issue. I also have not been able to replicate it using test spreadsheets.

  • Post Points: 20

Verified Answer

[Infragistics] Milko
Points 21,365
Infragistics Employee
Answered (Verified) Replied On: Mon, Oct 16 2017 5:18 AM Reply
Verified by Jesse

Hi Jesse,

Thank you for your reply and sample provided.

I have tested this on my side and was able to reproduce the issue you had described. I have asked our developers to examine this further. To ensure it will receive attention I have logged this behavior in our internal tracking system with a development ID of 244273. This will be assigned to a developer in a future sprint to review my investigation and confirm my findings or to offer a fix, or other resolution.

In the mean time you can fix this by calling Recalculate method of the workbook with first parameter set to TRUE like this:

this.wb.Recalculate(true);

Please let me know if you need more information.

Sincerely,
Milko
Associate Software Developer

  • Post Points: 20

All Replies

[Infragistics] Milko
Points 21,365
Infragistics Employee
Replied On: Thu, Oct 12 2017 2:50 AM Reply

Hello Jesse,

I have tested this behavior in all supported version and cannot reproduce it. The cell value is always correct after I import the workbook. As you said you were not able to reproduce this with a test workbook, therefore I can only assume this is somehow related to your original workbook. Can you send us this workbook so we can try to reproduce this issue at our side? If we succeed we will be able to look further into this, otherwise there is not much we can do.

Looking forward to your reply and workbook provided.

Sincerely,
Milko
Associate Software Developer

  • Post Points: 20
Jesse
Points 65
Replied On: Thu, Oct 12 2017 10:41 AM Reply

Attached are 2 spreadsheets. The Estimate2 being the template used to fill in the data and extract the values. The 2016Ritzville spreadsheet is after the template is loaded, filled with data, and exported to disc. All values on export show up as expected, but the values in memory do not work.

The Example Formula I'm using can be found @ Summary D 4. 

=ROUND(IF(OR(Detail!$A$28, Detail!$A$29), Detail!E$12, IF(Detail!$A$30, Detail!E$13, IF(Detail!$A$31, Detail!E$14, Detail!E$13))), 6)

Which evaluates to 0 in memory. See image: https://www.dropbox.com/s/z5v7sy9jhhyac1q/DetailIfOr.PNG?dl=0 

If we break down the formula (images below) it should read

=ROUND(IF(OR(FALSE, FALSE), 0.840876, IF(FALSE, 0.840876, IF(TRUE, 0.843876, 0.843876))), 6) 

IMAGES @ RUNTIME

DetailA28: https://www.dropbox.com/s/nnaau0lzgyfa3rs/DetailA28.PNG?dl=0 

DetailA29: https://www.dropbox.com/s/si4n7v53b1vg5d0/DetailA29.PNG?dl=0 

DetailA30: https://www.dropbox.com/s/ovrp22jf3lhqgrh/DetailA30.PNG?dl=0 

DetailA31: https://www.dropbox.com/s/jx86pwm85infmzp/DetailA31.PNG?dl=0 

DetailE12: https://www.dropbox.com/s/vwx6dyaxkkjjwi1/DetailE12.PNG?dl=0 

DetailE13: https://www.dropbox.com/s/f7h3ma4rawcges9/DetailE13.PNG?dl=0 

DetailE14: https://www.dropbox.com/s/asv5b7zcmg7hu4c/DetailE14.PNG?dl=0 

  • Post Points: 20
[Infragistics] Milko
Points 21,365
Infragistics Employee
Replied On: Fri, Oct 13 2017 2:17 AM Reply

Hi Jesse,

I have tested this with the worksheet you provided and was unable to reproduce the behavior you are describing. I have tested this with all supported versions of Infragistics Excel Library. Attached is the sample I have used to test this behavior as well as a short video I recorded during my test. Please run the sample at your side and let me know what the result of this test is. Also, if my sample does not show correctly what you are trying to achieve feel free to modify it so it can reproduce this behavior. Then send it back to me for further investigation.

Looking forward to your reply.

Sincerely,
Milko
Associate Software Developer

  • Post Points: 20
Jesse
Points 65
Replied On: Fri, Oct 13 2017 3:56 PM Reply

Thanks for the reply and testing. Yes I did the same thing to try to repro this issue, but the steps taken are not exact here. 

We load the Estimate2.xlsx into memory;

Then we set some sheets cells to updated values from the database.

Then we read out the formula values from the spreadsheet. 

This workflow I can reproduce. If I load the sheet. Read some values, then change values in the woorkbook at runtime.. the referencing columns in the sheet do not seem to update. 

I have updated your example. to Load the estimate2.xlsx file at start and read out the "happy path" values. ALL false, null, or 0 at that time. 

Clicking the Change Values button simulates the issue i'm running into where I change the value of Detail A31 to true, which should force the D3 Formula to re-evaluate to read the value from E18 with is set to 10. But you can see that this doesn't happen. In fact, none of the formulas seem to re-evalutate on the fly. I have tried to force a workbook recalculate but that does nothing either. I assume this is a change to how/where the calc-engine does its work. 

  • Post Points: 20
[Infragistics] Milko
Points 21,365
Infragistics Employee
Answered (Verified) Replied On: Mon, Oct 16 2017 5:18 AM Reply
Verified by Jesse

Hi Jesse,

Thank you for your reply and sample provided.

I have tested this on my side and was able to reproduce the issue you had described. I have asked our developers to examine this further. To ensure it will receive attention I have logged this behavior in our internal tracking system with a development ID of 244273. This will be assigned to a developer in a future sprint to review my investigation and confirm my findings or to offer a fix, or other resolution.

In the mean time you can fix this by calling Recalculate method of the workbook with first parameter set to TRUE like this:

this.wb.Recalculate(true);

Please let me know if you need more information.

Sincerely,
Milko
Associate Software Developer

  • Post Points: 20
Jesse
Points 65
Replied On: Mon, Oct 16 2017 10:47 AM Reply

Thanks so much Milko, this is working on my end. Guess I missed the overloaded constructor on the Recalculate() method. Thank you again. 

-Jesse.

  • Post Points: 5
Page 1 of 1 (7 items) | RSS