4.5.1.1 Setting Column Values

Summary

Origin provides several ways to fill a worksheet column with values. Use Auto Fill or script commands to fill a series of values. Use the F(x) row or the Set Values dialog box to define a mathematical formula to generate or transform a data set. Refer to values in other columns from the same sheet or from other sheets and books. Select from a large collection of built-in functions to compute values. Create variables from metadata stored in worksheets or column headers, and use these variables in your column formula.

This tutorial will show you how to compute column values by:

• Filling a Column with an Arithmetic Series
• Using Built-in Functions
• Using Other Columns
• Using Cell Values
• Using Variables from Workbook Metadata

Filling a Column with Arithmetic Series

Origin provides multiple methods to fill a column with arithmetic series.

Using Auto Fill

1. Enter a few starting values in cells.
2. Select the two cells.
3. Move the mouse to the bottom right-hand corner of the second cell. The cursor will change to display "+".
4. 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. To repeatedly copy values instead of generating new values, hold down the CTRL key and drag the mouse toward the bottom of the column.

Using Filling A Set of Numbers

1. 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
2. Enter 23 in the To edit box. Enter 2 in the Increment edit box
3. 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.

1. Create a new workbook. Import US Metropolitan Area Population.dat from the \Samples\Data Manipulation\ folder.
2. Add a new column to the worksheet (right-click 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.
3. To calculate the population density, enter the expression, B/A, in the F(x) row of column E.
4. The column will get computed using data from the other two columns.

Using Built-in Functions

1. Create a new workbook. Import Step Signal with Random Noise.dat from the \Samples\Signal Processing\ folder.
2. We are going to calculate the moving average of column B, that is, calculating the adjacent average value at each point of column B.
3. Click the Add New Columns button on the Standard toolbar to add a new column C. Highlight this column and right-click, and then click Set Column Values... to open the Set Values dialog.
4. In Set Values dialog, click the Search and Insert Functions button to search for keyword adjacent average.
5. Double click function name Movavg(vd,back,forward) to insert it into dialog and close the dialog.

6. Highlight the characters vd. replace vd with B, replace back with 0 and replace forward with 2. Your formula should look like this:
 Notes: You can also use the wcol(1) or Col(A) menu to use other columns in the worksheet

7. 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, short name, or long name 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.

1. Open the project Samples\Data Manipulation\Setting Column Values.OPJ and switch to the Columns from Other Sheets subfolder.
2. Right-click on the worksheet tab labelled Sample and select Duplicate Without Data. Rename(by double-clicking on the current name) the new sheet as: Corrected Sample.
3. Now you will fill these three columns with data based on formulas that reference columns in the other sheets. Highlight the first column and right-click on it to select Set Columns Values to open the dialog. Select Variables: Add Range Variables 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.
4. 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 in the appeared Insert Mode dialog box.
5. "range r1 = Sample!Col(A);" will be automatically inserted into the Before Formula Scripts panel. Please rename it as:
range rTime = Sample!Col(A);
Note:There will also be an alternative script which uses index in expression. This script is commented and will not be executed.
6. Then enter rTime in the Column Formula and click the OK button to generate data for the first column and close the dialog
7. Highlight column B and column C and right-click on them, select Set Multiple Column Values to open the dialog. Then select Variables: Add Range Variable by Selection and insert two range variables one by one(column B in the Sample and Reference sheet) to the Before Formula Script panel similarly as the previous steps. Rename them as:
range rSample = Sample!Col(B);

and
range rRef = Reference!Col(B);
8. 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(_ThisNumCol) in both lines so it looks as follows:
range rSample = Sample!wcol(_ThisColNum);

range rRef = Reference!wcol(_ThisColNum);
9. Then input the following expression into the Column Formula:
rSample - (rSample[1] - rRef[1])
10. Click the OK button to generate data for the column B and column C of the Corrected Sample worksheet.
 1. You reference a particular cell value with square brackets, so [1] in the formula above means the first element. 2. You can select Formula: Save and Formula: Load in the Set Column Values dialog to save your formulas and reload it 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.

1. Open the project \Samples\Data Manipulation\Setting Column Values.opj and switch to the Cells in a Worksheet subfolder in Project Explorer.
2. Right-click on column C and select the Set Column Values... context menu to bring up the Set Values dialog.
3. 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 .
Click OK for the appeared Insert Mode dialog to add its expression to the Before Formula Scripts panel.
4. 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 X-Function and passes the cell values from column G as arguments for polynomial order and number of points, which controls the Savitzky-Golay smoothing performed during the differentiation.
5. The Set Values dialog then should be as following:
6. 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.