The Set Values dialog box takes a one-line, user-defined expression and fills one or multiple worksheet columns (or portions of columns) with values generated by the expression. The expression -- entered directly in the Column Formula box -- can incorporate arithmetic operators, column and cell references, functions, user-defined variables, constants, etc. (see below). The Set Values Dialog features syntax-coloring.
Optional Scripting Tabs:
 | To load examples:
|
| Beginning with Origin 2018 SR0, cell-level (as opposed to column-level) calculations can be performed. See Using a Formula to Set Cell Values. |
Beginning with Origin 2017 SR0, Spreadsheet Cell Notation (SCN) is enabled by default. When SCN is ON, you can use the simplified SCN notation or you can use the pre-2017 notation in either the Set Values Column Formula box or in the the F(x)= column label row. Note, however, that when using the older "col" or "wcol" syntax, some limitations apply.
When opening Origin files (OPJ, OGW, etc.) saved prior to Origin 2017, SCN will be OFF in the workbook. However, even in older files, SCN can be enabled via the Window Properties dialog box. If you do not enable SCN (you leave the Spreadsheet Cell Notation box unchecked), you must use the older column and cell notation in the Set Values and F(x)= formulas throughout the affected workbook.
Note that the new spreadsheet cell notation can only be used in the Column Formula box and in the F(x)= label row. It cannot be used in the Before Formula Scripts box of Set Values or in LabTalk scripts elsewhere in Origin.
Within a given worksheet, column and cell references are now made in the following way:
A; // reference to column A in the same sheet A1; // reference to column A, row 1, in the same sheet
See Column Formula Examples, below.
| Note: To refer to a column or cell of Text format, add "$" at the end.
|
Prior to Origin 2017, you had to define a range variable to use data in other sheets and books, in your Set Values formulas. As noted in the table above, using spreadsheet cell notation you can make direct references to data in other sheets and books. This is supported in both the F(x)= label row and the Set Values Column Formula box. Spreadsheet cell notation must be enabled in the target book(s).
Use the following syntax:
1!A; // reference to column A in first sheet in the same book Sheet1!A; // reference to column A in a named sheet ([Sheet1]) in the same book [Book1]1!A; // reference to column A in the first sheet in another book ([Book1]) [Book2]Sheet1!B2; // reference to row 2 of column B, in a named sheet ([Sheet1]) in another book ([Book2])
See Column Formula Examples, below.
| By default, Origin does not automatically substitute the sheet short name when entering references such as |
Expressions can include any of the following. Note that range variables must be predefined (e.g. in Before Formula Scripts) before you can use them in the Column Formula expression:
| Data References | Variables | Operators | Functions | Constants |
|---|---|---|---|---|
†Must be predefined in Before Formula Scripts panel, Script Window, etc.
| Note that the Set Values menu commands wcol(1), Col(A), Functions and Variables are useful for browsing and inserting data references, functions, variables and constants into your Column Formula or Before Formula Scripts. Selected elements are inserted at the cursor. As of Origin 2017 SR0, these menu functions only support the older column and cell notation (not spreadsheet cell notation). |
| There is a quick way to load a conditional control or loop script when you are doing script in Before Formula Script box. Right click on Before Formula Script box to select Conditional/Loop at the bottom of the context menu, and then select a conditional structure or loop you desired in the flyout. The syntax will be added at cursor with simple comments. |
For help with Set Values dialog box controls, see Menu Options and Dialog Controls.
These are examples of "stand alone" expressions that can be used in the Column Formula box.
| New Spreadsheet Cell Notation | Older Notation | Description |
|---|---|---|
| B - C | col(B)-col(C) | Returns the difference between col(B)[i] and col(C)[i]. |
| 2!B - 3!C | N/A | Returns the difference between sheet index 2, col(B)[i] and sheet index 3, col(C)[i]. |
| [Book2]Sheet1!A - [Book3]Sheet1!A | N/A | Returns difference between [Book2]Sheet1!A[i] and [Book3]Sheet1!A[i] |
| [Book2]Sheet1!A - [Book3]Sheet1!A2 | N/A | Returns difference between [Book2]Sheet1!A[i] and [Book3]Sheet1!, cell A2 |
| sin(pi*B) | sin(pi*col(B)) | Returns the sine of col(B)[i] times pi. |
| today() | no change | Returns the current date. |
| A$ + B$ | col(A)$ + col(B)$ | Internally converts column A and B into strings and concatenates them. |
| A>0? A: Na() | col(A)>0? col(A): Na() | If col(A)>0, returns value in col(A), otherwise returns missing value (see LabTalk Utility Function, Na()). |
| B-B1 | col(B)-col(B)[1] | Subtracts the first point in column B from all other values in column B. |
| A + StartTime | N/A | Adds the named range "StartTime" value to values in column A. |
| total(A[i-1:i+3]) | total(col(A)[i-3:i+3]) | Returns the sum of a sub-range from i-3 to i+3 in col(A), where i is the row index. |
| sum(A:C, D:G, F) | N/A | Returns the row-wise sum of a values in columns A to C, D to G, and F. |
| (wcol(j)*2)-1 | no change | Can be used to transform the jth column (all selected columns) by multiplying each value by 2 and subtracting 1. |
| Note: When using i and j in Set Values or in the F(x)= column label row, you must refer to them using lowercase letters. Uppercase I and J will be interpreted as worksheet column short names. |
| Use "end" or "0" to define the last cell in a column, e.g. |
Starting with Origin 2022, the column formula box in Set Values and F(x)= label row get autocompletion support for LabTalk-supported functions and named ranges.
If you do not wish to use the feature, you can disable it for column and/or cell formulas/F(x)=:
If you want to reuse your Set Values formulas, along with Before Formula Scripts and Python Function code, you can:
in Set Values. To reload your formula in Set Values, click Formula: Load and choose your formula. With the column label row F(x)= cell selected, choose Column: Fill with User Formula or right-click in the cell and choose Load.When using the new simplified syntax, you can define a column formula in column C that refers to values in column A and column B, then insert a column between column A and B and column references in the formula will update as column short names are reassigned. However, this behavior is not supported under any of the following conditions:
When a column formula is affected by any of these conditions, the column formula will not be updated.
The following short tutorial will show you how to use this dialog to generate data for a simulated gaussian curve.
on the Standard toolbar.
. You will see that column B is highlighted in the worksheet.
button, the graph is also updated.)
| Origin supports using a sub-range of a column as function argument in the Set Values dialog. For example: To calculate the sum of a sub-range from i-3 to i+3 in column A (where " i " is the row index), you can enter a formula in the Column Formula edit box. Total(A[i-3:i+3]) |
The following short tutorial will show you how to use this dialog to set values for multiple columns simultaneously.
on the Standard toolbar.
to import the files F1,dat and F2.dat in the <Origin Folder>\Samples\Import and Export\ path. In the impASC dialog, set Multi-File (except 1st) Import Mode to Start New Books and click OK.
on the Standard toolbar to create another workbook.
to add a column. Highlight all columns, select Column: Set Multiple Columns Values from the main menu or right-click the columns to select Set Multiple Columns Values in the context menu to open the Set Values dialog.
. Enter below scripts in the Before Formula Scripts edit box,
range r1=[F1]F1!wcol(j); //"j" is the column index. range r2=[F2]F2!wcol(j);
| When defining variables in Before Formula Scripts, use lower-case letters for variable names (e.g "r1" not "R1"). Use of upper-case letters will generate an error like the following: Column short name restriction is on. R1 refers to cell and cannot be defined as variable. Failed to create operation for Book1_G due to error in Set Column Value scripts. |
Python functions can be called from the Column Formula box or from the Before Formula Scripts tab, depending upon your needs. Those functions can be defined in an external file or they can be defined in the Python Function tab of Set Values. The advantage to defining them in the Set Values dialog is that they are saved with the project; or with a template for repeat use.
Python functions are defined on the tab in the form of ...
def ff(a)
... and called on the Before Formula Scripts tab or in the Column Formula box as ...
py.ff(a)
For more information on using Python in the Set Values dialog see Using Python in Tools and Dialogs > Set Column Values.
| If the F(x)= cell (1) begins "py." and (2) calls a Python function defined in an external file, you can right-click on the F(x)= cell and choose Open Python File to open the Python function in the Code Builder IDE |
This simple tutorial will show you how to define a function on the Python Function tab and call it in the Column Formula box:
on the Import toolbar.
on the Standard toolbar.
.
import numpy as np from scipy import signal def smooth(y,npts, norder): """ F:Fii Perform Savitzky-Golay smoothing using scipy signal """ #npts: number of points for smoothing window #norder: polynomial order y=signal.savgol_filter( np.array(y), npts, norder ) return y
py.smooth(B,101,3)
