2.93 FAQ-941 Why are my cell formulas not working?

Why-are-cell-formulas-not-working

Last Update: 1/8/2019

Origin 2017 introduced "Spreadsheet Cell Notation" (SCN) 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, SCN must be turned on in the workbook.
• In versions Origin 2017 through Origin 2019, SCN is ON by default for new workbooks and this icon displays in the upper-left corner of the worksheet when SCN is ON. Beginning with Origin 2019b, this icon is hidden, though SCN remains ON, by default, for new workbooks.
• If you see this icon in the upper-left corner of the workbook (ANY version), right-click on the workbook title bar and choose Properties. On the Properties tab, check the Spreadsheet Cell Notation box and click OK.
• Spreadsheet Cell Notation can be turned on in any book or project, even those that were created before Origin 2017. Right-click on the workbook title bar and choose Properties. On the Properties tab, check the Spreadsheet Cell Notation box and click OK.

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:

1. From the menu, choose Edit: Edit Mode.
2. 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 window's Spreadsheet Cell Notation box is checked and Origin's Edit Mode is turned off.

1. Choose Window: Script Window, type the following and press Enter:

@esc=

2. If Origin returns a value of 0 (@esc=0), then type the following and press Enter:

@esc=1

Also, if @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:

1. From the menu, choose Preferences: System Variables to open the Set System Variables dialog box.

Keywords:calculation, spreadsheet, @RCN, @ESC, page.xlcolname, ClrX