4.6.9 Using a Formula to Set Cell ValuesUsingFormulaetoSetCellValues
Cell Formula
Origin supports cell formula in both data cells and userdefined column label cells in worksheet since Origin 2018.
 Start the formula with = sign in cell.
 Use Origin's builtin 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[1]  column A, row 1
 A0  column A, last row
 A[i]  column A, current row
 Use This[1], This[i] for rows in current column

 A1:A10, A[1:10]  column A, row 110
 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[i2:i1]  current column, two rows before current row

 A[C]$  column A, string of Comments cell
 A[EID]$  column A, string of userdefined parameter EID
 A[D1]  column A, value of 1st userdefined 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[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.
Origin

Excel

Description

=B1  C1

=B1C1

Difference between B1 and C1.

=B  B0

N/A

Difference between the current row of column B and last row of column B.

=B1mean(B)

=B1average(B:B)

Difference beween B1 and average of column B

=mean(This)

NA

Average of current column

=B1$ + C1$

=B1 & C1

Concatenate strings in B1 and C1.

=IF(A1<98.6, A198.6, NA())

=IF(A1<98.6, A198.6, NA())

if A1 <= 98.6, return A198.6, otherwise return a missing value (see LabTalk Utility Function, Na()).

=col(A)[D1]*A1

N/A

The value of column A Userparameter 1 multiplied by value in column A, row 1

=lookup(This[element]$, [book1]1!1, [book1]1!2)$

N/A

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)

=page.v1*B1

N/A

The value of system variable v1 (stored with page) multiplied by value in column B, row 1

=A1page.info.File1.Info.FileDate

N/A

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 datetime format. Note differences in Origin and Excel behavior.

=total(A[1:3])

=SUM(A1:A3)

Sum of A1 to A3.

=Total(wcol(j1)[1:3])

N/A

Sum of row 1 to 3 in the immediate left of current column.

=total(Sheet2!A1:J10)+total(Sheet1!A1:J10)

=SUM(Sheet2!A1:J10)+SUM(Sheet1!A1:J10)

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)[1]  [Book3]Sheet1!col(A)[1]

N/A

Difference between A1 on different books with the old col(name)[row] syntax

=[Book1]Sheet1!col(A)[1] + StartTime

N/A

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)[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 autoadjusting 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 bottomright 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 builtin column label rows such as Long Name, Units, Comments, etc. They are only supported in Userdefined 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 multicolumn 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 Excellike 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 rightclick 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 rightclicking on your User Parameter row heading and choosing Edit from the shortcut menu. You can edit cell formulas individually by doubleclicking directly into a User Parameter row cell.
Related System Variables
Enable or disable worksheet cell formula.
1  enable, 0  disable
Controls use of Excelstyle multicell references incorporating the colon character ":", in worksheet cell formulas (e.g. "=total(A1:A10)" or "=total(A1:C1)"
1  enable, 0  disable
Note: Originstyle references such as "=total(A[1:10]) are not affected. However, there is no multicolumn support with this notation. For multicolumn range specification you must use Excelstyle 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
See Also
