We are able to find value errors in the sample excel and code provided by us earlier. Please check formula in S4 …Evaluate the formula =IFERROR(MATCH(S$2,Sheet2!$A:$A,0),"") … MATCH(S$2,Sheet2!$A:$A,0) is giving Value Error and showing empty string.
If enter on formula window in office 365.it is resolving the value error
Please check all scenarios .It was working earlier with release 19.8 …now completely broken.we are seeing many value error in our workbook. Cant we back track why code is broken??.I cannot give all kind of formulas…Even it is failing when we copy other formulasValueError.png (68.5 KB)
@taruney,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-47007 – Value error shown while evaluating the formula
We need Fix for Value Errors as earliest possible…we are seeing breaking functionality because of fix…we don’t know after fix where else it will fail.it is a serious concern…things are working in 19.8 release after that it is broken.
@taruney,
As we logged the ticket some days ago, so could you spare us a little time (3-5 days or so) for complete evaluation and investigation for your issue. If it is not much complex, you should be receiving the fix before the end of next week or even before it. If the issue is complex, it might take a couple of weeks.
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version (in the next 2-3 days or so) after performing QA and incorporating other enhancements and fixes.
The Scenario shared in the Sample file is fixed…now few other formulas are giving value errors. Difficult to reproduce all type of formulas in Sample code…Please check , Is there any way u can verify will all kind of formulas and circular references
Please create a sample console application, zip the project and post us here to show the issue, we will check it soon. Also attach your template file and a few screenshots to highlight the problematic areas. This will help us really to evaluate the issue precisely to consequently figure it out soon.
@taruney,
Here is some additional information about the problem:
Such kind of issue is complicated and only occurs for some special combinations of different functions, so it is impossible for us to make our test cases cover all scenarios in the real world. We keep improving our formula parser to make it work in the similar way as in MS Excel, however, the rules for working with formulas in MS Excel are varied for different formulas and are hard to be identified.
Commonly such kind of issue is caused by some internal data of some function used in the formula. When one bug is found, we make changes for the function data of the formula and surely make the fix work fine for all existing cases. However, the change we do for the function data to fix the bug may be incorrect for some other special formulas in the real world which uses this function in some other sequence and combination with other functions.
If you find such kind of issue for other formulas, please provide the formulas and we will test and check whether we can reproduce the issue by the formula itself. If the issue cannot be reproduced, then we will require more details, such as template file, executable code, etc., to investigate the issue further.
Furthermore, we recently improved our formula parser further and fixed some bugs. We will provide a fix for you soon and then you may test whether those issues have been fixed by it or not.
Please find the sample code and template and one of the formula getting this issue “”=-SUM(IF(S$24<>“Champ”,-S242,IF(S$10="",0,INDEX(‘Sheet2’!$F$1:$H$7,6,S$10))),S242)" SampleCodeAndTemplate.zip (119.1 KB)
Please give the fix as generic…tomorrow if new formula comes in production, fails again then it is a big problem.Please try to have fix which works for any combination of formulas
We will evaluate it soon. Also, we do not promise to provide the next fix tomorrow. Anyways, once the fix is ready or available for public use (after passing certain test cases and QA), we will share it with you immediately.
Please find couple of formulas causing the issue.
1.=MAX(S206+MIN(L208-L196,0)-R207,0) (able to reproduce with sample)
2.=IF($S198<0,0,-IF(L196>0,MIN(-L206,$S198),0)) (not able to reproduce with sample)
Sample Code and template
Same ScenarioSampleCodeAndTemplate1.zip (139.1 KB).
Few are simple formulas …please try to fix generic way…if i get issue in production …it is difficult.(19.8.0 was stable …i am not seeing any issue with formulas,don’t know how code is broken 19.10.0 onwards)
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.