#ERROR! message when using IFS function.

Questions
1

Hello,

I’m having an issue with a formula and I’m hoping someone can point me in the right direction on what’s going on. I’m parsing an excel spreadsheet. The formula is “=IFS(C5<C7,“Pass”,C5>=C7,“Fail”)” and it works fine in excel, but shows up as #ERROR!” when shown in my react website. I was able to output the formula and it’s showing. “Parsing error. Redundant input, expecting EOF but found: (“

I also outputted the value and it looks like this: “=_xlfn.IFS(C5<C7,“Pass”,C5>=C7,“Fail”)” which appears to be fine.

Any help is much appreciated.

Thanks,
Mike

2

Hi @mhess , thanks for reporting this issue. I believe this is due to the parser failing when it encounters the _xlfn. prefix that Excel adds to certain functions for backwards compatibility. I’ll review this with the team and update this thread when I have more info.

For now, you can just strip out the _xlfn. prefix before passing the formula:

formula.replace(/_xlfn\./g, '')

There are a few other prefixes that Excel uses. Here’s a regex to remove them all:

formula.replace(/_xlfn\.SINGLE\.|_xlfn\._xlws\.|_xlfn\.|_xlws\.|_xlpm\./g, '')

Hope this helps! Thanks again for reporting the issue. Please let me know if you still see the same error after removing the _xlfn prefix.

3

Just wanted to write a follow up, thank you for your help, doing the replace did solve the issue.

Thanks again,

Mike

1 Like
4

Awesome, glad to hear it! Thanks for following up.

Here’s the issue in GitHub. I’ll close out this topic once the fix is live.