OriginLab Corporation - Data Analysis and Graphing Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis     
 
Skip Navigation Links
All BooksExpand All Books
TutorialsExpand Tutorials
Data AnalysisExpand Data Analysis
Data ManipulationExpand Data Manipulation
Setting Column ValuesExpand Setting Column Values

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.
    Tutorial85SetValues AutoFill1.png
  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 "+".
    Tutorial85SetValues AutoFill2.png
  4. Drag the mouse toward the bottom of the column. The column will be filled with 1, 3, 5, 7, ... .
    Tutorial85SetValues AutoFill3.png

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
    TutorialSetValues FillANumberValues.png
  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.
    Set value fx.png
  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 Button Add New Columns.png 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 Search Functions Set Column Value 2015.png to search for keyword adjacent average.
    Set Column Value Adjacent Average.png
  5. Double click function name Movavg(vd,back,forward) to insert it into dialog and close the dialog.
    Tutorial Set Column Value Function Insert.png
  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


    Tutorial81SetValuesFunction.png


  7. Click OK. The last column will fill with the moving average from column B.
    Tutorial81SetValuesFunctionResults.png


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 expand.png 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
    SetValuesRangerTime.png
  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])
    TutorialSetValuesRangerRef.png
  10. Click the OK button to generate data for the column B and column C of the Corrected Sample worksheet.
    SetValuesRangeResults.png

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 Button expand.png.
    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:
    SetValuesUseCellSetting.png
  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.

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.

  1. Open or continue working with \Samples\Data Manipulation\Setting Column Values.OPJ, and switch to the Worksheet Metadata subfolder from the Project Explorer window.
  2. Select column A and right-click to select the Insert menu option. A new column is inserted to the left of column A.
  3. Select the first column (this newly inserted column) and right-click on it. Then select the Set Column Values menu item to open the Set Values dialog.
  4. Select the Variables: Add Info Variable menu item to open the Insert Variables dialog. Select Numeric int from the Variable Type drop-down 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.
    InsertPageInfoVairale.png
  5. 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.
  6. In the upper Column Formula panel, input {d1:d2:d1+(n1-1)*d2} and then press the OK button to generate data and close the dialog. The column will be filled with frequency values.
  7. Highlight the first and second columns, right-click 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:
    SetValuesPageInfo2.png
 

© OriginLab Corporation. All rights reserved.