# 4.6.9 Using a Formula to Set Cell Values

## Cell Formula

Origin supports cell formula in both data cells and user-defined column label cells in worksheet since Origin 2018.

• Use Origin's built-in Labtalk functions
• Access data cell, label cell, column, named range, metadata, variables, constants, etc. in calculation
• Drag formula to extend it to other cells
• Auto adjustment of formula when col/row is inserted or deleted or when it's copied to other location ## Column Cell Reference Syntaxes

Spreadsheet Cell Notation (SCN) must be enabled in the workbook so that user can refer column and cell with similar notation as Excel.

Column Cell Range Label Row
• A - column A
• This - current column
• A1, A - column A, row 1
• A0 - column A, last row
• A[i] - column A, current row
• Use This, This[i] for rows in current column
• A1:A10, A[1:10] - column A, row 1-10
• A1:B0 - column A row 1 to column B last row
• A[i:i+2] - column A, current row to 2 rows after it
• This[i-2:i-1] - current column, two rows before current row
• A[C]$- column A, string of Comments cell • A[EID]$ - column A, string of user-defined parameter EID
• A[D1] - column A, value of 1st user-defined parameter
• Use This[C]$, This[EID]$, This[D1] for label cell in current column Add sheetname!, sheetindex!, [bookname]sheetname! or [bookname]sheetindex! before the column cell reference if data is on different sheet or book Add $after the column cell reference for string e.g. A1$, A$For subrange, : must be within the [ ]. So A:A & A:D are not supported Use A[1:10] for same column, or A1:D10 for different columns Add$ before column or row part for absolute reference. See Extending Formulas Across Rows or Columns section below. Use == to build range string and expression involving range string, if current A1 contains Book name. e.g. =="["+A1$+"]"+"1!C1" will refer to C1 cell of 1st sheet in a specified book in A1. =="1000+["+A1$+"]"+"1!C1*0.3" will will be an expression based on such C1 cell.

## Examples

### Extending Book Part

== syntax also works to concatenate book, sheet, column and row part to form a complete cell reference.

In the following example Book short names are put to column A and how to use cell formula to fill column B with B2 values in each book.

In B1 cell, put =="["+A1\$+"]"+"Sheet1!B2". Drag the bottom-right corner of B1 cell and drag down to 12th row so B2 in 12 books are filled. ## Limitations

Here are some known issues when using cell formula

• Importing an MS Excel file will not bring in your Excel formulas. Only calculated values are imported. See Working with Microsoft Excel.
• Cell formulas are not supported in the built-in column label rows such as Long Name, Units, Comments, etc. They are only supported in User-defined Parameter rows.
• Variable i and j are used for row and column indexing in Origin (see System Variables) so A[i], col(A)[i], wcol(j), wcol(j)[i] are still supported in cell formula but doesn't not work well with extending formula in some cases etc.
• wcol(j) refers to jth column may not be reliable since the value of J is undetermined except in a multi-column formula, e.g. it doesn't work when used in function argument to return a string. Try to use This instead when you can.
• Extending a cell formula across many rows and columns can strain system resources and may cause Origin to freeze. In many cases, you can accomplish the same task using more easily using Set Column Values

## Formula Bar

The View: Formula Bar introduced in Origin 2021 is an Excel-like bar allowing you to enter or view formula or on cell or column formula/expressions. While it is not necessary to use the Formula Bar to enter cell expressions, it does offer the advantages

Note: Change the font size by changing the value of system variable @FBFS (default is "130").

## User Parameter Row Formula

Beginning with Origin 2019, you can right-click on the column label row headings, Add User Parameters and name and define a formula for the entire row. This is handy for calculating key statistics (e.g. mean, std. deviation) for each column of numbers in a worksheet. The column formula uses the placeholder "This" as a wildcard reference to each column in the worksheet (see next section).

Once a row formula has been created, you can edit the Name and Formula by right-clicking on your User Parameter row heading and choosing Edit from the shortcut menu. You can edit cell formulas individually by double-clicking directly into a User Parameter row cell. ## Related System Variables

• @esc

Enable or disable worksheet cell formula. 1 - enable, 0 - disable

• @xlr

Controls use of Excel-style multi-cell references incorporating the colon character ":", in worksheet cell formulas (e.g. "=total(A1:A10)" or "=total(A1:C1)"
1 - enable, 0 - disable

Note: Origin-style references such as "=total(A[1:10]) are not affected. However, there is no multi-column support with this notation. For multi-column range specification you must use Excel-style notation (e.g. "=total(A1:D10)").

• @fac

Controls autocomplete support in column formulas (Set Values) and cell formulas
0 - disable for both, 1 - enable for cell formula, 2 - enable for column formula, 3 - enable for both