We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Improper Formula generated for simple Spreadsheet formula in Excel spreadsheet

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)),""),"")
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.
Same calculations as excel spreadsheet generates in Excel
Invalid calculation and Error

Attachment: New_Compressed_(zipped)_Folder_e770382b.zip

6 Replies

AA Arulraj A Syncfusion Team January 18, 2019 10:14 AM UTC

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. 
Arulraj A  

AA Arulraj A Syncfusion Team January 22, 2019 12:35 PM UTC

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. 

Arulraj A 

SP Spotty February 12, 2019 07:09 PM UTC

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,

AA Arulraj A Syncfusion Team February 13, 2019 08:40 AM UTC

Hi Spotty, 
Thanks for your update. 
We have tested your reported scenario using previously provided sample using our latest Syncfusion product version 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. 
Arulraj A 

SP Spotty February 13, 2019 05:32 PM UTC

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

AA Arulraj A Syncfusion Team February 14, 2019 04:14 PM UTC

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. 

Arulraj A 

Live Chat Icon For mobile
Up arrow icon