4.6.8.1 Quick Start


Set Values Dialog Box Key Features

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.

SCV dialog features8.png

While its usage is optional, the Before Formula Scripts box increases the power of the Set Values dialog. This box takes one or more lines of LabTalk script and runs the script before the expression in the Column Formula box is executed. This makes possible such things as pre-processing of input data, defining functions, variables and constants for use in the Column Formula box, etc. For more information on the use of Before Formula Scripts, see Entering Expressions in the Set Column Values Dialog. For help with Set Values dialog box controls, see Menu Options and Dialog Controls.

To load examples in the Set Values dialog box, click Formula, Load Sample > and choose from the list of sample formulas.

Beginning with Origin 2018 SR0, cell-level calculations can be performed. See Using a Formula to Set Cell Values.

Spreadsheet Cell Notation

Beginning with Origin 2017 SR0, Spreadsheet Cell Notation is enabled by default and is indicated by the presence of this icon Spreadsheet cell notation mode.png in the upper-left corner of the workbook. When you see this icon, you can use the new 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 pre-2017 files (OPJ, OGW, etc.), no spreadsheet notation icon displays in the workbook, though it can be enabled via the Window Properties dialog box. If spreadsheet cell notation is disabled (box is unchecked), you must use the older column and cell notation in Set Values and F(x)= formulas.

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 or in LabTalk scripts elsewhere in Origin.

References to Columns and Cells

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.

References to Other Sheets and Books

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 2!a2 into the F(x)= row or into a cell formula. You can modify this behavior by changing the value of LabTalk System Variable @SCVU=0.

What Can I Enter in the Column Formula or Before Formula Scripts Boxes?

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.

Column Formula Examples

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 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.
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.
(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.

If you want to use your Set Values formula again, there are a couple of easy ways to do that:

  • Save the formula to your User Files Folder by clicking Formula: Save As on the Set Values menu bar. To reuse, click Formula: Load and choose your formula.
  • Save the formula with a workbook template. When you open an instance of the workbook template, your Set Values formula(s) will be ready for use.

A Note: When Column Formulas do not Automatically Update

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:

  • The column formula uses the col() or wcol() functions.
  • Your column formula uses a script entered into the Before Formula Scripts box in Set Values.
  • Your column formula contains brackets "{}".
  • Your column formula contains sheet index references as opposed to sheet name references (e.g. 1!A vs. Sheet1!A).

When a column formula is affected by any of these conditions, the column formula will not be updated.

Additional Information

Two Quick Examples

Set Values for a Single Column

The following short tutorial will show you how to use this dialog to generate data for a simulated gaussian curve.

  1. Create a new workbook by clicking the New Workbook button New Workbook.png on the Standard toolbar.
  2. Highlight column A and right-click on it to select Set Column Values from the short-cut menu to open the Set Values dialog.
  3. Enter {-1:0.03:5} in the Column Formula edit box and then click the Apply button. Column A should be filled with a series of numbers.
  4. Then we will use the one of the navigation buttons to let you input an expression for the second column without closing the dialog. Click the Next button Next Button.png. You will see that column B is highlighted in the worksheet.
  5. Now enter 1 + (5/(1.5*sqrt(PI/2)))*exp(-2*((A-2)/1.5)^2) in the Column Formula edit box and choose Auto in the Recalculate drop-down list.
  6. Click the OK button. The Set Values dialog is closed. You will see the results in the following worksheet.
    (If you create a line graph of column B, you will get a graph similar to the one next to the workbook.)
    Quick Start SetColumnValues 1.png
  7. Double click on the F(X) column label row of column B, and change the formula to 1+5/(1.5*sqrt(PI/2))*exp(-2*(A-2)/1.5*2). You will find the value in the column B is updated automatically.
    (Click the Rescale Button Rescale.png button, the graph is also updated.)
    Quick Start SetColumnValues 2.png

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])

Set Values for Multiple Columns

The following short tutorial will show you how to use this dialog to set values for multiple columns simultaneously.

  1. Create a new project by clicking the New Project button Button New Project.png on the Standard toolbar.
  2. Click the Import Multiple ASCII button Button Import Multiple ASCII.png 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.
  3. Two workbooks will be created, named as F1 and F2. Click the New Workbook button New Workbook.png on the Standard toolbar to create another workbook.
  4. With the 3rd workbook active, click Add New Columns button Button Add New Columns.png 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.
  5. Expand the bottom panel by clicking the Show Scripts button Button Show Scripts.png. 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);
  6. Enter (r1+r2)/2 in the Column Formula edit box
  7. Select Options: Direct Edit Formula Cell to uncheck the option.
  8. Select Options: Formula Text... and enter (F1+F2)/2 in the Formula Text dialog, then click OK button.
  9. Click the OK button in the Set Values dialog. You will see the results in the worksheet, and (F1+F2)/2 will display in the F(x) column label row instead of the formula.

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.