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. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Improper Formula generated for simple Spreadsheet formula in Excel spreadsheet

Thread ID:

Created:

Updated:

Platform:

Replies:

142085 Jan 18,2019 12:50 AM UTC Feb 14,2019 04:14 PM UTC WinForms 6
loading
Tags: Spreadsheet
Spotty
Asked On January 18, 2019 12:50 AM UTC

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 [Syncfusion]
Replied On 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. 
 
Regards, 
Arulraj A  


Arulraj A [Syncfusion]
Replied On 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 


Spotty
Replied On 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,

Arulraj A [Syncfusion]
Replied On 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 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
Replied On 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

Arulraj A [Syncfusion]
Replied On 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. 

Regards, 
Arulraj A 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon