A custom function does not recalculate in Excel Online if the "at" (@) symbol has been used with the IF function

[s-ratashnyuk]

Provide required information needed to triage your issue

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Office on the web
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 16.0.17618.42300
• Operating System: Mac OS 14.4.1
• Browser (if using Office on the web): Chrome 123.0.6312.59, Safari 17.4.1 (19618.1.15.11.14)

Expected behavior

All functions recalculate

Current behavior

In Velixo sometimes we have to recalculate programmatically the sheet to make all values actual. Custom function, for example =@IF($B27 = ""; "";SI.WRITEBACKBUDGET($C$5;$F$12;$F$13;FALSE;$H$12:$I$13;$B27;;$D$6:$E$7;;$I$4:$T$4;$I27:$T27)) does not recalculate when we call context.workbook.worksheets.getActiveWorksheet().calculate(true);. It means Excel even does not start to execute the function. If we remove the @ before IF, functions become recalculating.

Steps to reproduce

• see the video in the Useful logs section
• open the file provided in the "Links" section, in Excel online
• make a copy to be able to edit the file
• install Script lab extension
• import gist and register functions: https://gist.github.com/s-ratashnyuk/c3c124f2bb01d8b074a0c506d6d85bb6
• click every cell with !VALUE or !NAME to recalculate
• import gist and run it: https://gist.github.com/s-ratashnyuk/23f99e2e44148e4d3a886e06918e0ad6
• press the "Run" button and observe the bug is reproduced
• change the formula in the F6 cell: remove the "at" (@) sign before IF
• press the "Run" button and observe the bug is not reproduced

Link to live example(s)

1. The test file: https://velixo-my.sharepoint.com/:x:/p/sratashnyuk/EVdyU3MDhthCqswqK1naHNgBi_RfOA6e-lguI3-085u27A?e=R7tVPy

Provide additional details

I figured out that for the bug reproduction, the sheet has to contain spill range functions and in the buggy formula we have to use range references that contain IF in their formulas.

Context

In large sheets with complicated tables, it's important to use "@" and also have the ability to recalculate a sheet before, for example, uploading data to an external database.

Useful logs

• how to install script lab, snippets and reproduce the bug: https://velixo-my.sharepoint.com/:v:/p/sratashnyuk/ES4bQVqw3rZEmQZC4iXfKKkB_0_FvU4pvyhucjUFcJ9ARw?nav=eyJyZWZlcnJhbEluZm8iOnsicmVmZXJyYWxBcHAiOiJPbmVEcml2ZUZvckJ1c2luZXNzIiwicmVmZXJyYWxBcHBQbGF0Zm9ybSI6IldlYiIsInJlZmVycmFsTW9kZSI6InZpZXciLCJyZWZlcnJhbFZpZXciOiJNeUZpbGVzTGlua0NvcHkifX0&e=wfeJL8

[microsoft-github-policy-service[bot]]

Thank you for letting us know about this issue. We will take a look shortly. Thanks.

[MiaofeiWang]

Hi @s-ratashnyuk , thanks for reporting this. I am able to reproduce the issue on Excel on web with the steps you provided (thanks for the details). We have created internal work item 8935445 to track it.

Just to double confirm, this issue doesn't exist on Excel for Windows or Excel for Mac, right? I tried on both (replacing the body of function incrementBywith Math.random() for Windows), and with or without @, custom function can recalculate correctly.

[s-ratashnyuk]

Hi @MiaofeiWang! Thank you so much for your answer! Yes, the bug is reproduced only in Excel online and does not exist on Desktop versions. Have a nice day!