Improper Formula generated for simple Spreadsheet formula in Excel spreadsheet

[Spotty]

I have an excel spreadsheet that is using some simple formulas to lookup and calculatetotals. These work correctly within excel and when this spreadsheet is loaded within the Spreadsheet control but as soon as an edit is made which would effect the reclaculation they result in an "Improper Formula" message in the cells.

Some example of the formula is

=IFERROR(IF(ISNUMBER(SEARCH("Video",$D8)), IF($C8="","",IF(VLOOKUP($E8,$B$40:$B$52,1,FALSE),1,0)),""),"")

=IFERROR(IF($C8="","",IF(VLOOKUP($E8,$B$40:$B$52,1,FALSE),1,0)),"")

In the attached spreadsheet go to the 9am tab and change the name from spotty to something else - the sum columns for this line should show 1,"",1 in the respective columns.

Now load the worksheet into a spreadsheet control and do the same action. results in 0,Improper Formula, Improper Formula.

EXPECTED RESULT

Same calculations as excel spreadsheet generates in Excel

ACTUAL RESULT

Invalid calculation and Error

Attachment: New_Compressed_(zipped)_Folder_e770382b.zip

[Arulraj A]

Hi Spotty, 

Thanks for using Syncfusion product. 

We are able to see the reported problem, we have forwarded this to our development team to validate and we will update you the details on 22 January 2019. 

 

Regards, 

Arulraj A  

[Arulraj A]

Hi Spotty, 

Thanks for your patience. 

We have logged the bug report for this issue. We have planned to fix this issue for our next release. The fix will be available with our upcoming release 2018 Volume4 SP1 which will be expected at February first week. 

Regards, 

Arulraj A 

[Spotty]

I've just updated to latest versions and I still believe the problem is present or a variation of it.  I'll create a simple repro later to validate it but I did the action in Excel and everthing worked OK and within the spreadsheet control I was generating formula errors.  So something is still not quite right,

[Arulraj A]

Hi Spotty, 

 

Thanks for your update. 

 

We have tested your reported scenario using previously provided sample using our latest Syncfusion product version 16.4.0.52. But all are working fine. So, please let us know your exact use case to reproduce your reported scenario it will be helpful us to provide the solution at the earliest. 

 

Regards, 

Arulraj A 

[Spotty]

Attached is a spreadsheet - Load inside a spreadsheet control and go to 9am tab, Go to cell E17 as an example and type 5.   Then look at the totals at the bottom and you will see many now display a #VALUE error.  (Problem.png included)

Open the spreadsheet in Excel and do exactly the same task and you will see that the cells are correctly calculated.

I believe you will see there is a difference and a problem.

Attachment: ManifestLoadOrder20190213_42793b52.zip

[Arulraj A]

Hi Spotty, 

Thanks for your update. 

We can reproduce your reported scenario. We have logged the bug report for this issue “Issue in calculation of SumIFS formula when use the empty string”. It will be available on our upcoming main release 2019 Volume 1 which is planned in mid of March 2019. 

Regards, 

Arulraj A