4.5.1.1 Set Column/Cell ValuesSetColVal
Summary
Origin provides several ways to compute a column or matrix of values. One of Origin's most powerful features is Set Column Values, a tool for performing mathematical operations, generally on values stored in a workbook or matrix. These operations can make use of Origin functions, mathematical and logical operators, builtin or userdefined variables, and can even allow for preprocessing of input data.
This tutorial will show you how to compute cell or column values by:
 Filling a Column with an Arithmetic Series
 Using Builtin Functions
 Using Other Columns of Values
 Using Cell Values
 Using Variables from Workbook Metadata
In addition, you will learn:
 How to enter a formula into worksheet cell to compute cell values
 Auto behaviors of setting cell values
Setting Column values
Filling a Column with Arithmetic Series
Origin provides multiple methods to fill a column with arithmetic series.
Using Auto Fill
 Enter a few starting values in cells.
 Select the two cells.
 Move the mouse to the bottom righthand corner of the second cell. The cursor will change to display "+".
 Drag the mouse toward the bottom of the column. The column will be filled with 1, 3, 5, 7, ... .
 Note that a row can also be auto filled by dragging towards the right. In addition, to copy a sequence of cell values to other column or row cells, press SHIFT while selecting the desired sequence, then press CTRL and drag.

Using Filling A Set of Numbers
 Right click on the column B and select Fill Column with: A Set of Numbers from the context menu to bring up the PatternN dialog
 Enter 1 in the From edit box and 23 in the To edit box. Enter 2 in the Increment edit box

 After you click the OK button, Column B will be filled with values: 1, 3, 5, 7, ...., 23
Using Other Columns
We will show you how to enter expressions in the F(x) row to set column values.
 Create a new workbook. Import US Metropolitan Area Population.dat from the \Samples\Data Manipulation\ folder.
 Add a new column to the worksheet (rightclick to the right of the last column in the worksheet and select Add New Column from the context menu). Change the Long Name of the column to Population/Sq. Mi.
 To calculate the population density, enter the expression, A/B, in the F(x) row of column E.
 The column will get computed using data from the other two columns.
Using Builtin Functions
 Create a new workbook. Import Step Signal with Random Noise.dat from the \Samples\Signal Processing\ folder.We are going to calculate the moving average of column B, that is, calculating the adjacent average value at each point of column B.
 Click the Add New Columns button on the Standard toolbar to add a new column C. Highlight this column and rightclick, and then click Set Column Values... to open the Set Values dialog.
 In Set Values dialog, click the Search and Insert Functions button to search for keyword adjacent average.
 Double click function name Movavg(vd,back,forward) to insert it into dialog and close the dialog.
 Highlight the characters vd. replace vd with B, replace back with 0 and replace forward with 2. Your formula should look like this:
 Click OK. The last column will fill with the moving average from column B.
 When referring to another column in the same worksheet, you can use index (e.g. "col(1)"), short name (e.g. "A" or "col(A)"), or long name (e.g. "signal with noise") to identify the column.

Using Columns from Other Sheets
The Set Values dialog provides an Variable menu to easily insert range variables that point to columns in other books/sheets, which can then be used to compute column values for the current column.
 Open the project \Samples\Data Manipulation\Setting Column Values.opj and click on the Columns from Other Sheets subfolder.
 In the workbook, rightclick on the worksheet tab labelled Sample and select Duplicate Without Data. Rename(by doubleclicking on the current name) the new sheet as: Corrected Sample.
 Now you will fill these three columns with data based on formulas that reference columns in the other sheets. Highlight the first column and rightclick on it to select Set Columns Values to open the dialog. Select Variables: Add Range Variable by Selection to open the Select from Worksheet dialog. With this dialog, you could select a column from worksheet and insert it as a range variable to the Before Formula Script panel.
 When the Select from Worksheet dialog is open, activate the Sample sheet, highlight column A to select and click the button to confirm selection and click OK (accept Column Notation) in the Insert Mode dialog box that appears.
 "range r1 = Sample!Col(A);" will be automatically inserted into the Before Formula Scripts panel. Edit the formula to read as:
range rTime = Sample!Col(A);
 Then enter rTime in the Column Formula and click the OK button to generate data for the first column and close the dialog.
 In the Corrected Sample worksheet, highlight column B and column C and rightclick on them. From the shortcut menu, select Set Multiple Column Values to open the dialog. Select Variables: Add Range Variable by Selection and insert two range variables, one by one, to the Before Formula Script panel similarly as the previous steps. Edit these new entries as follows:
range rSample = Sample!Col(B);
and
range rRef = Reference!Col(B);
 Now we will edit the range variables in the Before Formula Scripts panel and use another expression to get the same results. Remove the column names Col(B) of the two range variables and select Variables: Predefined Variables: wcol(j) in both lines so it looks as follows:
range rSample = Sample!wcol(j);
range rRef = Reference!wcol(j);
 Then input the following expression into the Column Formula:
rSample  (rSample[1]  rRef[1])
 Click the OK button to generate data for the column B and column C of the Corrected Sample worksheet.
  You reference a particular cell value with square brackets, so [1] in the Column Formula expression above means the first element.
 You can select Formula: Save and Formula: Load in the Set Column Values dialog to save your formulas and reload them into other columns to generate new data.

Using Cell Values
Values contained in specific worksheet cells can be referenced and used to compute the formula for setting column values. This provides an easy way to use worksheet cells as control cells for updating values in a column.
 Open the project \Samples\Data Manipulation\Setting Column Values.opj and switch to the Cells in a Worksheet subfolder in Project Explorer.
 Rightclick on column C and select the Set Column Values... context menu to bring up the Set Values dialog.
 Use the Variables: Add Range Variable by Selection menu item to open the Select from Worksheet dialog. Then select column G(Value) in this worksheet, click the button.
Click OK when the Insert Mode dialog appears (accept Column Notation) to add its expression to the Before Formula Scripts panel.
 In the Before Formula Scripts panel, change the name of the range variable to be rControl and add these additional lines so that the script looks like below
range rControl = Col(G);
//range r1 = Col(7);
int nOrder = rControl[2];
int nPoints = rControl[3];
differentiate se iy:=(1,2) order:=1 smooth:=1 poly:=nOrder npts:=nPoints
oy:=(1,3);
The script calls the differentiate XFunction and passes the cell values from column G as arguments for polynomial order and number of points, which controls the SavitzkyGolay smoothing performed during the differentiation.
 The Set Values dialog then should be as following:
 Click OK to close the dialog and see the results in column C. Now you can try to change the values in column G, to change the output.
Note: Allowed values of polynomial order are 1 to 9.
 The graph shown in the worksheet was first created and then embedded into the worksheet by merging a group of cells.

Using Variables from Workbook Metadata
Metadata stored in the workbook, such as variables saved when importing data using the Import Wizard, can be referenced and used for computing column values.
 Open or continue working with \Samples\Data Manipulation\Setting Column Values.OPJ, and switch to the Worksheet Metadata subfolder from the Project Explorer window.
 Select column A and rightclick to select the Insert menu option. A new column is inserted to the left of column A.
 Select the first column (this newly inserted column) and rightclick on it. Then select the Set Column Values menu item to open the Set Values dialog.
 Select the Variables: Add Info Variable menu item to open the Insert Variables dialog. Select Numeric int from the Variable Type dropdown list. Expand the USER.VARIABLES node and click to highlight NUMBEROFPOINTS row with Value as 3800. Press the Insert button to insert this variable into the Before Formula Scripts panel.
 Next, set Variable Type to Numeric double. Hold the Shift key down to select both StartFrequencyKHz and StepFrequencyKHz, and then press Insert to insert these two variables. Press the Close button to close the dialog.
 In the upper Column Formula panel, input {d1:d2:d1+(n11)*d2} and then press the OK button to generate data and close the dialog. The column will be filled with frequency values.
 Highlight the first and second columns, rightclick on them and select Set As: XYY to change the plotting designations to X and Y. After you change the long name of the first column to Frequency, the worksheet should look like:
Setting Cell Values
In the CellEdit Mode, you can enter a cell formula beginning with an equals sign "=" into a cell (a data cell or UserDefined Parameter Row cell) just as below.
Once the formula has been entered, you exit edit mode (inplace edit or Edit: Edit Mode) to see the resulting cell value.
Let us use an example to show you how cell formulas work in Origin:
 Open a new workbook in your Origin Project.
 Import the sample data "automobile2.dat" located at the folder <Origin Program Folder>Samples\Statistics into the workbook.
 Rightclick on the header cell of column label row "F(x)=" and select Add User Parameters from the context menu to add a user parameter.
 Here, let's add two user parameters and enter Mean and Std Dev as their parameter names separately. And then, add one more column at the end of this worksheet, enter Power/Engine Displacement as the column Long Name.
 Select Edit: Edit Mode menu to switch to the Edit Mode. And then, in the cell Mean and Std Dev of Col("Power"), enter =mean(this) and =stddev(this) respectively. Once the edit done, click outside the cell to exit celledit mode reselect the Edit: Edit Mode menu item to display cell formula results.
Note: About the meaning of the variable "This", please refer to this page.

 Click the cell Mean of Col("Power") to select it, put the cursor to the lower right corner of this cell. When the cursor turns to a cross +, grab this "+" handle and drag with your mouse horizontally to Col("Engine Displacement"). Release the cursor, you will found other cells of this row will be filled with some result values. Alternately, you could simply doubleclick on the "+" handle and copy the formula to all Mean row cells to the right of Col("Power").
 Doubleclick those Mean cells, you will found the filled formulas are all same, =mean(this).
 Do the same thing for the Std Dev row to calculate the standard deviation for all these five columns.
 Go to the last column we just added, in the first cell enter =E1/I1.
Note: Here, E1 means the first cell of col(E) and I1 means the first cell of col(I). You can refer to this page to learn more about the formula.

 Release your cursor to execute the division. Use the same way at Step 6 to grab this "+" handle and drag with your mouse vertically to the end cell of this column (Hint: If you hover on the lowerright corner of the cell, directly over the cell handle, the cursor changes to a "+" sign. When this "+" sign appears, doubleclick to extend the formula to the end of the column. This is faster and easier than dragging with your mouse).
 Release the cursor to get all division results of col(E)/col(I) at each row.
Select Edit: Edit Mode menu, you can check and edit all these cell formulas.
