4.6.8.1 Quick StartWksSetColValQuickStart
Set Values Dialog Box Key Features
The Set Values dialog box takes a oneline, userdefined 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, userdefined variables, constants, etc. (see below). The Set Values Dialog features syntaxcoloring.
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 preprocessing 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.

Spreadsheet Cell Notation
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 pre2017 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.
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.
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()).

BB1

col(B)col(B)[1]

Subtracts the first point in column B from all other values in column B.

total(A[i1:i+3])

total(col(A)[i3:i+3])

Returns the sum of a subrange from i3 to i+3 in col(A), where i is the row index.

sum(A:C, D:G, F)

N/A

Returns the rowwise 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 j^{th} 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.
 Create a new workbook by clicking the New Workbook button on the Standard toolbar.
 Highlight column A and rightclick on it to select Set Column Values from the shortcut menu to open the Set Values dialog.
 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.
 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 . You will see that column B is highlighted in the worksheet.
 Now enter 1 + (5/(1.5*sqrt(PI/2)))*exp(2*((A2)/1.5)^2) in the Column Formula edit box and choose Auto in the Recalculate dropdown list.
 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.)
 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*(A2)/1.5*2). You will find the value in the column B is updated automatically.
(Click the Rescale button, the graph is also updated.)
 Origin supports using a subrange of a column as function argument in the Set Values dialog.
For example: To calculate the sum of a subrange from i3 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[i3: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.
 Create a new project by clicking the New Project button on the Standard toolbar.
 Click the Import Multiple ASCII button to import the files F1,dat and F2.dat in the <Origin Folder>\Samples\Import and Export\ path. In the impASC dialog, set MultiFile (except 1st) Import Mode to Start New Books and click OK.
 Two workbooks will be created, named as F1 and F2. Click the New Workbook button on the Standard toolbar to create another workbook.
 With the 3rd workbook active, click Add New Columns button to add a column. Highlight all columns, select Column: Set Multiple Columns Values from the main menu or rightclick the columns to select Set Multiple Columns Values in the context menu to open the Set Values dialog.
 Expand the bottom panel by clicking the Show Scripts button . 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);
 Enter (r1+r2)/2 in the Column Formula edit box
 Select Options: Direct Edit Formula Cell to uncheck the option.
 Select Options: Formula Text... and enter (F1+F2)/2 in the Formula Text dialog, then click OK button.
 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 lowercase letters for variable names (e.g "r1" not "R1"). Use of uppercase 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.

