2.94 FAQ-941 Why are my cell formulas not working?
Last Update: 10/26/2018
Origin 2017 introduced "spreadsheet cell notation" for use in column-based calculations in the Set (Column) Values dialog box. Origin 2018 extended use of spreadsheet-like notations to cell formulas (cell-level calculations).
Occasionally, users disable all or part of the cell formula functionality, either via the GUI or LabTalk command and forget that they have done so. Later, they attempt to make use of the cell formulas, then find that they do not work.
Spreadsheet Cell Notation Must be Enabled in the Workbook
- For cell formula calculations to work, spreadsheet cell notation must be turned on in the workbook.
- If spreadsheet cell notation is turned on, you will see this icon in the upper-left corner of the worksheet.
- If the spreadsheet cell notation icon looks like this , click on it and choose Spreadsheet Cell Notation (SCN) On from the popup menu.
- If the spreadsheet cell notation icon is missing from the upper-left corner of the workbook, right-click on the workbook title bar and choose Properties. On the Properties tab, check the Spreadsheet Cell Notation box and click OK. This box will appear even in older workbooks, once the book is opened in Origin 2017 or later.
Note that there are LabTalk (script) methods for disabling spreadsheet cell notation for a single book and for disabling it for every existing book in the project, including new books. These are covered in FAQ-849 Can I turn off or selectively control use of spreadsheet cell notation in my workbooks?
Controlling Cell Formulas via the GUI or LabTalk Command
There are several ways to partially or totally disable cell formulas in the Origin project (see FAQ-939 for details).
Controlling Cell Formulas via the GUI
Make sure that Edit Mode is not simply enabled:
- From the menu, choose Edit: Edit Mode.
- If there is a check mark beside Edit Mode, click the menu item to clear it.
Controlling Cell Formulas via LabTalk Command
Make sure that LabTalk System Variable
@ESC = 1. If
@ESC=0, cell formulas (e.g. "=A1+B1") will be treated as simple text and expressions will not resolve. This will happen even if the workbook's Spreadsheet Cell Notation box is checked (the icon is displayed) and Origin's Edit Mode is turned off.
- Choose Window: Script Window, type the following and press Enter:
- If Origin returns a value of 0 (@esc=0), then type the following and press Enter:
@ESC returns a value of 0, check the Set System Variables dialog to see if you have an "ESC" entry that is set to 0:
- From the menu, choose Tools: System Variables to open the Set System Variables dialog box.
Keywords:calculation, spreadsheet, @RCN, @ESC, page.xlcolname, ClrX