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                     
 

4.5.1.1 Set Column/Cell 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

Beginning with version 2018 SR0, Origin supports to use a formula to set cell values.

Then, this tutorial will also show you:

  • How to Enter Formula in 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

  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.We are going to calculate the moving average of column B, that is, calculating the adjacent average value at each point of column B.
  2. 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.
  3. 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
  4. Double click function name Movavg(vd,back,forward) to insert it into dialog and close the dialog.
    Tutorial Set Column Value Function Insert.png
  5. Highlight the characters vd. replace vd with B, replace back with 0 and replace forward with 2. Your formula should look like this: Tutorial81SetValuesFunction.png
  6. 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 (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.

  1. Open the project \Samples\Data Manipulation\Setting Column Values.opj and click on the Columns from Other Sheets subfolder.
  2. In the workbook, 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 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.
  4. When the Select from Worksheet dialog is open, activate the Sample sheet, highlight column A to select and click the Button Hunt Done.png button to confirm selection and click OK (accept Column Notation) in the Insert Mode dialog box that appears.
  5. "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);
  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. In the Corrected Sample worksheet, highlight column B and column C and right-click 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);
  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(j) in both lines so it looks as follows:
    range rSample = Sample!wcol(j);
    range rRef = Reference!wcol(j);
  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 Column Formula expression 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 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.

  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 the Button Hunt Done.png button.
    Click OK when the Insert Mode dialog appears (accept Column Notation) 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

Setting Cell Values

In the Cell-Edit 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.

SettingCellValues 01.png

Once the formula has been entered, you exit edit mode (in-place 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:

  1. Open a new workbook in your Origin Project.
  2. Import the sample data "automobile2.dat" located at the folder <Origin Program Folder>Samples\Statistics into the workbook.
    SettingCellValues 04.png
  3. Right-click on the header cell of column label row "F(x)=" and select Add User Parameters from the context menu to add a user parameter.
    SettingCellValues 03.png
  4. 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.
    SettingCellValues 02.png
  5. 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 cell-edit mode reselect the Edit: Edit Mode menu item to display cell formula results.
    SettingCellValues 05.png
    Note: About the meaning of the variable "This", please refer to this page.
  6. 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.
    SettingCellValues 06.png
  7. Double-click those Mean cells, you will found the filled formulas are all same, =mean(this).
    SettingCellValues 07.png
  8. Do the same thing for the Std Dev row to calculate the standard deviation for all these five columns.
  9. Go to the last column we just added, in the first cell enter =E1/I1.
    SettingCellValues 08.png
    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.
  10. 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 lower-right corner of the cell, directly over the cell handle, the cursor changes to a "+" sign. When this "+" sign appears, double-click to extend the formula to the end of the column. This is faster and easier than dragging with your mouse).
    SettingCellValues 09.png
  11. Release the cursor to get all division results of col(E)/col(I) at each row.
    SettingCellValues 10.png
  12. Select Edit: Edit Mode menu, you can check and edit all these cell formulas.
    SettingCellValues 11.png

Note: Additionally, you are also allowed to extend formulas acroos rows and columns.For more details about setting cell values with formula, please refer to this page.
 

© OriginLab Corporation. All rights reserved.