OriginLab Corporation - Data Analysis and Graphing Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis                     
 

4.4.6 Using a Formula to Set Cell Values

Set Cell Values 1.png

Beginning with version 2018 SR0, 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 and returns a 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.
=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.

Referring to Worksheet Columns using "This"

Origin has a special word - "This" - that is a placeholder for the current worksheet column. "This" can be used in cell formulas and column formulas and it is particularly useful in formulas added to User-defined Parameter row cells in situations where you have multiple columns of data and you want to obtain a certain statistic for each column:

  1. Create a User-defined Parameter row.
  2. In the first User-defined column cell, type 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, grab the "+" handle in the lower right corner of the cell and drag with your mouse to extend your "This" formula to other User-Parameter row cells.
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. It cannot be used to represent rows 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

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 copy a cell formula to other cells, column and row references are adjusted in relative fashion. That is, if you copy a formula "=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 copy 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

From Origin 2018 SR0, 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>
 

© OriginLab Corporation. All rights reserved.