4.6.9 Using a Formula to Set Cell Values

Set Cell Values 1.png

Beginning with version 2018, Origin supports cell-level calculations.

The cell formula mechanism discussed here is not the only way to do math operations on cell values. Origin has a Set Values feature for doing row-wise math operations involving entire columns of data. Set Values is not only faster -- it has such things as syntax coloring and assistance with functions -- things that are quite helpful when building complex expressions. Plus, you can define scripts to do such things as conditioning data before your calculations are performed. For more information, see Set Column Values - Quick Start.

Cell Formula Notation

All cell formulas begin with an equals sign "=":

=A1 // returns value in column A, row 1
=A1+B1 // returns total of column A, row 1 and column A, row 2
=mean(A) // returns mean of column A
=total(A[1:10]) // returns total of cells A1 through A10
=mean(A1:J10) // returns the mean of cells in the range A1 through J10.
=date(A1, "dd.MM.yyyy HH:mm:ss.##") // takes date-time string of specified format in column A row 1, returns Julian day value

Edit Mode and Display of Cell Formulas

The worksheet has an Edit Mode (from the menu, Edit: Edit Mode):

  • Edit Mode allows for display and easy editing of cell formulas.
  • When Edit Mode is toggled off, cell formulas are hidden and instead, the resulting cell value is displayed (note that in this mode, you can double-click on a cell to edit the formula).
Set Cell Values 2.png

The only portion of the column label row (worksheet header area) to support cell formulas are User-defined Parameter rows. The other thing to note about data in column label rows is that, despite what it appears to be (numeric, date & time), it is treated as string data. Therefore, to use a column label row cell reference in a cell or column formula you must convert it to numeric data using something like the LabTalk value() function (e.g.=A1+value([D1]$)).

You don't have to toggle Edit Mode on to see the formula behind a particular cell value. When the cell is selected, the formula will display in the lower-left corner of the Status Bar.

Set Cell Values Status Bar.png

What Can I Enter in Worksheet Cell Formulas?

In addition to data references, your cell formula can include variables, operators, functions and constants.

Data References Variables Operators Functions Constants

Cell Formula Examples

These are examples of expressions that can be entered into the cell.

Origin Cell Notation Excel Notation Description
=B1 - C1 =B1-C1 Returns the difference between column B, row 1 and column C, row 1.
=B1$ + C1$ =B1 & C1 Concatenate strings in column B, row 1 and column C, row 1.
=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).
=B[i] - C[i] N/A Returns the difference between column B, row i and column C, row i.

See "Extending Formulas..." below.

=B1 - C[1] =B1+$C$1 Returns the difference between column B, row 1 and column C, row 1. The column B, row 1 reference is relative and when dragging to autofill other cells, row reference will increment down rows and column reference will increment across columns. The column C[1] reference is absolute and will not change when dragging down rows or across columns.

See "Extending Formulas..." below.

=Sheet2!B1 - Sheet3!B1 =Sheet2!B1 - Sheet3!B1 Returns the difference between Sheet2, column B, row 1 and Sheet3, column B, row 1.
=[Book2]Sheet1!A1 - [Book3]Sheet1!A1 =[Book2]Sheet1!A1 - [Book3]Sheet1!A1 Returns difference between [Book2]Sheet1!A1 and [Book3]Sheet1!A1.
=total(Sheet2!A1:J10)+total(Sheet1!A1:J10) =SUM(Sheet2!A1:J10)+SUM(Sheet1!A1:J10) Totals cells in ranges Sheet2!A1:J10 and Sheet1!A1:J10
=sin(pi*B1) =SIN(PI()*B1) Returns the sine of pi * column B, row 1
=date(A1, "dd.MM.yyyy HH:mm:ss.##") -- Origin takes date-time string of specified format and returns Julian day value. Note differences in Origin and Excel behavior.
=today() =TODAY() Origin returns the current date as a Julian day value. Note differences in Origin and Excel behavior.
=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()).
=total(A[1:3]) =SUM(A1:A3) Returns the sum of a column A, row 1 to column A, row 3.
=page.v1*B1 N/A Returns value of system variable v1 (stored with page) multiplied by value in column B, row 1

† Excel does not use Julian dates but instead uses a "serial number" system in which "time zero" is either January 1, 1900 (Windows default) or January 1, 1904 (Mac default). For information on converting Excel dates to Origin's Julian-based system, see FAQ-283: How do I convert date data from Excel to Origin?
‡ Origin's today() function returns today's date as a Julian-date value while Excel's TODAY() function returns today's date as an Excel serial number.

Note: The "$" string notation often confuses users. For instance, suppose you want to return a string value "boston" from cell A1:
=A1 // returns missing value (--)
=A1$ // returns "boston"
=upper(A1$) // returns "BOSTON"

Fore more information, see:

For side-by-side comparison of Origin and Excel functions, by category, see this page.

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.

User parameter formula.png

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.

Referring to Worksheet Columns using "This"

As mentioned in the previous section, "This" is a placeholder that is used to refer to the current dataset, i.e. the values in the associated worksheet column. We saw that "This" is used when creating a user parameter row formula and you can use "This" in your own cell and column formulas.

If you want to define a user parameter row formula and extend it across the entire row, define a Formula using the procedure outlined above. If you want to add a formula only to some user parameter cells -- you can do the following:

  1. Create a User-defined Parameter row.
  2. In the User-defined column cell where you want to add a formula, enter your formula using "This" to refer to the data in the current column (e.g. =max(This)  ).
  3. Click outside the cell to exit cell-edit mode. A calculated value should display.
  4. With the cell selected, hover on the lower right corner of the cell then (a) grab the "+" handle and drag with your mouse to extend the calculation to other worksheet columns or (b) double-click on the "+" handle to extend the formula to every row cell to the right of the original formula cell.
This Formula UPR.png

Note that "This" can also be used in worksheet data cells, as for instance, at the end of a column of data (e.g. "=total(This)"). It cannot be used to represent data in row-wise calculations.

Auto Adjustment of Cell Formulas When Inserting or Deleting Rows or Columns

Inserting or deleting of columns or rows triggers automatic adjustment of column formulas.

Set Cell Values 4.png

Extending Formulas Across Rows or Columns

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 the F(x)= cell or the (equivalent but more powerful) Set Values dialog box.

Once you have entered a cell formula into a cell, it is possible to extend the formula to cells in other rows and/or columns, (1) hovering on the lower-right corner of the formula cell and (2) when the cursor becomes a "+", drag with your mouse to extend down, across or both.

Origin Cell Formula Drag1.png
  • When you drag a cell formula to autofill other cells, column and row references are adjusted in relative fashion. That is, if you drag "=A1+B1" from cell C1 to cell D1, the formula in D1 is adjusted and becomes "=B1+C1". In such instances, cell references are said to be "relative."
  • To make absolute references to a cell value, place square brackets around the cell's row index number (e.g. "=A1+B[1]"). This will allow you to extend the formula to other cells without adjustment to the bracketed cell reference.
Cell Form Extend All.png

Putting brackets around a hard row reference (e.g. B[1]) creates an absolute cell reference to row 1. Putting brackets around a soft row reference (e.g. B[i]) does NOT create an absolute reference, as "[i]" is a variable that is recognized as a reference to the current row (i.e. a cell formula of "=B[i]" will return the value in column B, in the same row in which the cell formula appears). The following figure illustrates equivalent forms that will return the same values.

Cell Form Equivalent.png

Rather than dragging to extend a formula down a long column of values, hover over the lower right corner of the first formula-containing cell and double-click on the crosshairs.

Cell formula 2 click extend.png

Older Methods of Referencing Worksheet Cell Values

There are older methods for inserting cell values, variable values and strings into worksheet cells and, in a limited way, these methods will support calculations (e.g. var://max(col(A))*2). Most users will find the newer notation to be much more useful but we point these things out so that the user is aware.

Limitations to Use of Cell Formulas

Beginning with Origin 2018, cell formulas give users a way to perform cell-level calculations incorporating cell values, functions, variables and constants, using a syntax that is similar (but not identical) to MS Excel.

Things that are not supported:

  • Importing an MS Excel file will not bring in your Excel formulas. As of Origin 2018 SR0, only calculated values are imported. See Working with Microsoft Excel.
  • Cell formulas are not supported in the Long Name, Units, Comments or system Parameters rows of the Column Label Rows portion of the worksheet. They are only supported in User-defined Parameter rows.
  • In Origin, the variable i = row index and the variable j = column index (see System Variables). Formulas incorporating the row index variable i can be extended down a column by dragging (e.g. "=B[i] - C[i]") but formulas that combine multi-column range references and the variables i or j (e.g. "=total(A[i]:D[i])") are NOT supported.

There are "@" two system variables that the user should be aware of:

  • To disable cell formulas in the current session, open the Script window (Window: Script Window) and type:
@esc=0 <Enter>
  • To disable support for Excel-style notation (cell and column references such as "A1:C10") in the current session, open the Script window (Window: Script Window) and type:
@xlr=0 <Enter>