4.6.9 Using a Formula to Set Cell Values

Using-Formulae-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.

• Start the formula with = sign in cell.
• 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

To distinguish formula cells at first glance, select View: Highlight Formula/Linked Cells so that the cells will be highlighted with a different background color.

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.

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[1]:A[10] & A[1]:D[10] 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

The examples only show relative reference. Add $ before column or row part for absolute reference. See Extending Formulas Across Rows or Columns section below.

Extending Formulas Across Rows or Columns

Select the cell with formula and mouse over the lower right corner of it, when the cursor becomes a +, drag it to extend down, across or diagonally

Column and row reference in formula extends in relative fashion:

• when dragging vertically, only the row part changes

• when dragging horizontally, only the column part changes

• when dragging diagonally, both part will change

Place $ before the part to make absolute reference. E.g. to fix the row part, add $ before row part e.g. B$1. To fix both the column and cell, place a $ character before both the column and row parts, e.g. $B$1.

Double click the + is also supported. For data area, it will auto fill to bottom of the column. For label area, it will auto fill to the end of the row.

For row references in square brackets, e.g. sum(A)[1] or A[1], press Ctrl key when dragging to keep relative reference

Extending Sheet Part

Origin doesn't support relative sheet reference when extending formula. E.g. how to put B1 cell of all Sensor sheets to a new sheet? User may enter =Sensor01!B$1 or =1!B$1 and then expect to drag the formula to get =Sensor02!B$1, =Sensor03!B$1, ..., or =2!B$1, =3!B$1, ....

Workarounds:

• Variable i means row indexing, By putting $(i) before ! part, it can be used as sheet indexing

  

• Put sheet index or name in a column and use == syntax to concatenate it with column and row part to form a complete cell reference

  

In the above example, A1$ will convert A1 contents into a string. "!B1" is a literal string. ==A1$+"!B1" will concatenate them into =Sensor01!B1 which refers to B1 of Sensor01 sheet.

Note

• The sheet strings will need to be on the same page where you build your auto-adjusting sheet references.
• ==A1$+"!B1+10" will make an expression Sensor01!B1+10.
• If the sheet string contains special characters, such as "-", enclose the sheet name with double quotation marks: ==char(34)$+A1$+char(34)$+"!B1+10".

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.

Flash Fill

If the column(s) to the left shows a clear pattern, and you type in the expected result in the first cell of the target column, Origin will auto-detect the pattern, guess how you want to extract, combine, or reconstruct the string and number, and suggest the possible functions and formula in a pop-up preview. You can then populate the column with:

• plain values (Ctrl + Shift + E): static results that stay fixed when the source column(s) is changed.
• the generated formula: (Ctrl + Shift + Enter): dynamic results that will update automatically when the source column(s) is changed.

With "Flash Fill", you can:

• extract desired part from column

For example, you can split first name and last name from one column, or extract desired string from file path.

• combine columns

• some kinds of re-formatting

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)[1], 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

• easier way to enter and view long expressions that exceed cell width.
• search functions by clicking button to open the Search and Insert Functions dialog
• interactive range selection in expression by clicking on column heading, cell or drag to select a range. Note: not supported for different book
• define a named range by selecting a range and then type name on left side of formula bar
• locate a named range by selecting a ranged range on left side of formula bar

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.

You can copy a formula-defined user-parameter row to multiple worksheets by Apply User Parameters to mini toolbar button . See details here.

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

See Also

• Origin & Excel Functions Comparison
• Column Label Row Characters
• Auto Fill Cells
• Range Notation
• Insert links from other cells
• LT Supported String Functions
• Documentation for individual string and character functions