4.6.9 Using a Formula to Set Cell Values
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
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.
- 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.
The examples only show relative reference. Add $ before column or row part for absolute reference. See Extending Formulas Across Rows or Columns section below.
| =B1 - C1
|| Difference between B1 and C1.
| =B - B0
|| Difference between the current row of column B and last row of column B.
|| Difference beween B1 and average of column B
|| Average of current column
| =B1$ + C1$
|| =B1 & C1
|| Concatenate strings in B1 and C1.
| =IF(A1<98.6, A1-98.6, NA())
|| =IF(A1<98.6, A1-98.6, NA())
|| if A1 <= 98.6, return A1-98.6, otherwise return a missing value (see LabTalk Utility Function, Na()).
|| The value of column A User-parameter 1 multiplied by value in column A, row 1
| =lookup(This[element]$, [book1]1!1, [book1]1!2)$
|| Search the value of the element column label in a column and return the value of another column with the same index of the found element. (see the video under section User Parameter Row)
|| The value of system variable v1 (stored with page) multiplied by value in column B, row 1
|| A1 - minus the file date information of the imported file stored in page, suppose A1 is also a date
| =date(A1, "dd.MM.yyyy HH:mm:ss.##")
|| -- †
|| Julian day value of A1 in specified date-time format. Note differences in Origin and Excel behavior.
|| Sum of A1 to A3.
|| Sum of row 1 to 3 in the immediate left of current column.
|| Sum of A1 to J10 on both sheet1 and sheet2
| =Sheet2!B1 - Sheet3!B1
|| =Sheet2!B1 - Sheet3!B1
|| Difference of B1 on two sheets
| =[Book2]Sheet1!A1 - [Book3]Sheet1!A1
|| =[Book2]Sheet1!A1 - [Book3]Sheet1!A1
|| Difference between A1 on different books
| =[Book2]Sheet1!col(A) - [Book3]Sheet1!col(A)
|| Difference between A1 on different books with the old col(name)[row] syntax
| =[Book1]Sheet1!col(A) + StartTime
|| Adds the named range "StartTime" value to A1 of sheet1 on Book1
| =text(mean(B1:B10),".2")+" ± "+text(stddev(B1:B10),".4")
|| =(TEXT(AVERAGE(B1:B10),"0.00") & " ± " & TEXT(STDEV(B1:B10),"0.0000"))
|| Calculate mean and standard deviation of a range, then round to specified number of decimal places and convert to text. Concatenate, inserting string " ± " (e.g. 0.56 ± 0.2740).
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) or A, 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, ....
- 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.
- 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.
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.
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
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.
Related System Variables
Enable or disable worksheet cell formula.
1 - enable, 0 - disable
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)").
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