Excel Functions (ISREF and AND) do not work correctly

Excel Functions (ISREF and AND) do not work correctly

Posted by: dean.kinnear on 22 February 2024, 12:19 am EST

• 

  dean.kinnear

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 22 February 2024, 12:19 am EST

  AND Function:

  1) In any Excel worksheet, in cells A2 and A4, type the boolean value TRUE
  2) In cell A1, enter formula =AND(A2:A4)
  	The result in spreadjs is FALSE, but in Excel it is TRUE
  

  ISREF Function:
  
  =ISREF(INDIRECT(“‘SheetNameThatDoesNotExist’!A1”)) should produce FALSE, but returns TRUE

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 22 February 2024, 8:03 pm EST

  Hi Dean,

  I was able to replicate both the behaviors that you have mentioned with the latest version of SpreadJS V17.0.3.

  AND Function: You need to enable the Excel Compatible Mode to true to get the same result as Excel.

  Kindly use the following code snippet:

  // Set the Excel Compatible Mode for CalcEngine to True
  GC.Spread.CalcEngine.ExcelCompatibleCalcMode = true;

  Sample: https://jscodemine.grapecity.com/share/ced6mLiQiUauQNF17WpS3Q/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  You could refer to the following API to know more about Excel Compatible Calc Mode: https://developer.mescius.com/spreadjs/api/v15/modules/GC.Spread.CalcEngine#excelcompatiblecalcmode

  ISREF Function: I could observe the different formula result in SpreadJS and Microsoft Excel. It is a bug of SpreadJS and it has been escalated to the dev team for further investigation. The bug id for the issue is SJS-22896. Further, I will let you know when there is more info on this from the dev team.

  Regards,

  Ankit

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 21 March 2024, 7:34 pm EST

  Hi,

  The mentioned issue with internal tracking id: SJS-22896 related to the calculation of ISREF function has been fixed in the latest version of SpreadJS V17.0.5. Kindly upgrade to the latest version and let us know if you face any issues.

  Regards,

  Ankit

• 

  dean.kinnear

  • Post Options:
  • Link
    Copy

  Posted 25 March 2024, 1:14 am EST

  Any idea when this will be available to download? Thanks!

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 25 March 2024, 4:41 pm EST

  Hi,

  The SpreadJS V17.0.5 is released and you could use the following link to download it: http://cdn.mescius.com/spreadjs/17.0.5/Files/SpreadJS.Release.17.0.5.zip

  Regards,

  Ankit