Evaluating Formula Errors

Evaluating Formula Errors

Posted by: adisa.craig on 31 May 2019, 3:23 pm EST

• 

  adisa.craig

  • Post Options:
  • Link
    Copy

  Posted 31 May 2019, 3:23 pm EST

  Is there a way for me to get the message for why formulas fail. For example I was evaluating the following formula

  GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=DATEDIF(E5, E4, "M")')

  and I get a response of ```
  
  {_error: “#NUM!”, _code: 36}

  
  Is there a way for me to find out what the actual reason for the formula failure is such as in Google sheets the error message pops up that says "Function DATEDIF parameter 1 (6/17/2020) should be on or before Function DATEDIF parameter 2 (2/17/2020)."
  
   The message does not need to be this verbose but is there some sort of string values the the returned code represents

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 3 June 2019, 12:42 am EST

  Hi,

  The error strings in spreadJS represent the same error as it does in excel. ‘#NUM!’ error means that a formula or function contains numeric values that aren’t valid. In this case, date parameter1 is greater than parameter2.
  
  Following is a list of various error types in formulas:
  
  • #NUM! : a formula or function contains numeric values that aren’t valid
  
  • #VALUE! : a formula has the wrong type of argument.
  
  • #N/A : a formula can’t find what it’s been asked to look for. The most common cause of the #N/A error is with VLOOKUP, HLOOKUP, LOOKUP, or MATCH functions if a formula can’t find a referenced value.
  
  • #REF! : a formula refers to a cell that’s not valid
  
  • #NAME? : formula name is not recognized
  
  • #DIV/0! : a formula tries to divide a number by 0 or empty cell

  Regards
  
  Sharad

• 

  adisa.craig

  • Post Options:
  • Link
    Copy

  Posted 3 June 2019, 9:57 am EST

  Hi thanks for the response, but I was asking if there is a way to get more specific error messages than the ones above?
  
  As I mentioned on my original post Google Sheets gives the exact reason the formula failed. Is that possible in spreadjs?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 6 June 2019, 5:24 am EST

  Currently, the error messages behaviour of spread JS is the same as of MS Excel and we cannot customize it. We have added an enhancement request(internal tracking ID #273134) to support better error messages and added you as correspondence for the case. We will notify you once we have further updates regarding the same.

• 

  alex.risch

  • Post Options:
  • Link
    Copy

  Posted 14 June 2019, 9:56 am EST

  Hello,

  This has been to the product backlog, as of now there it is not a high priority as it does not match Excel functionality.

  Regards,

  Alex