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
Origin HelpExpand Origin Help
Workbooks Worksheets and Worksheet ColumnsExpand Workbooks Worksheets and Worksheet Columns
Simple Manipulation of Worksheet DatasetsExpand Simple Manipulation of Worksheet Datasets
Using Formulae to Set or Transform Worksheet Column ValuesExpand Using Formulae to Set or Transform Worksheet Column Values

4.4.5.3 Entering Expressions in the Set Values Dialog


Expression Basics

The expression used in the Set Values dialog should follow the logic of LabTalk. The expression can contain arithmetic operators, range variables, built-in LabTalk functions and user-defined functions etc. There are two places to define expressions:

  • Column Formula. Only a single-line expression can be input here.
  • Before Formula Scripts. Multi-line LabTalk scripts can be input here, which will be executed before column values are set with the formula in the Column Formula edit box.

Please note that several samples are available for you. You can select Formula: Load Sample to retrieve them.

SetColumnValue1.png

Useful Tips for Inputting Expressions

Beginning with Origin 2017 SR0, Spreadsheet Cell Notation is enabled by default and is indicated by the presence of this icon Spreadsheet cell notation mode.png in the upper-left corner of the workbook. When you see this icon, you can use the new notation or you can use the pre-2017 notation in either the Set Values Column Formula box or in the the F(x)= column label row. Note, however, that when using the col() or wcol() functions, using a Before Formula Script or when your expression uses brackets "{}", some limitations apply.

  • When refer to a column by its Short Name directly, the Short Name must be limited to no more than 3 characters. Otherwise such column reference cannot be recognized. For those Short Name exceed 3 characters, use old reference way (col() or wcol()) instead. The pre-2017 notation doesn't have this limitation.
  • The Variables menu can help you define variables used in the expression.
  • Origin offers many built-in functions under the Function menu, which can be directly used in the expression. The Search and Insert Functions dialog can be used to search for built-in functions.
  • You can also create a User-Defined Function yourself.
  • If use the new simplified column notation in a function with string parameter, such as Token, should use double quote instead of single quote.
  • The letters i and j are reserved system variables that store the current row index and column index, respectively. If you want to fill the column with row numbers or column index numbers, you can directly input i or j in Column Formula.
  • When using i and j in your Column Formula (or in an F(x)= cell), you must refer to them using lowercase letters to prevent conflict with column short names.
  • You can use LabTalk's Conditional operator to fill a column or range. For example (when the 4th column is the target), you can input col(3)==1?col(1):col(2) in Column Formula to either copy the values of the first column or the second column according to the values in the third column.
  • The {} notation can be used in Column Formula to fill the selected column(s) with a series of equi-spaced numbers.
  • {begin:step:end} fills the column(s) with numbers beginning at begin, ending at end and incrementing by step.
  • {begin:end} fills the column(s) with numbers beginning at begin, ending at end and incrementing by 1.
  • Data from other books and sheets can be accessed in several ways:
  • When setting values for Col(i), you can type in expression directly, rather than type in equation like "Col(i)= ..." in the formula.

Add Expressions only in Column Formula

You can add a single-line expression in the Column Formula edit box and leave the Before Formula Scripts panel empty. The expression in the Column Formula edit box can include built-in functions and user-defined functions. The following example shows how these functions can be used.

Example 1:

  1. Create a new workbook.
  2. Highlight column A. Right-click on it and select Fill With: Row Numbers.
  3. Highlight column B and right-click on it to select Set Column Values to open the Set Values dialog. Select Function: Math: Cos(x) to add cos() to the Column Formula edit box. Select Col(A): Col(A) . Then the expression will be cos(Col(A)). Click the OK button to execute the formula.

Add Expressions only in Before Formula Scripts

You can input multi-line LabTalk scripts in the Before Formula Scripts edit box and leave the Column Formula edit box empty. In addition to the built-in functions or user-defined functions, any other LabTalk scripts are supported here. So you can use range variables, string variables, loops, and LabTalk accessible X-Functions.

The following example will get the same results as Example 1.

Example 2:

  1. Create a new workbook.
  2. Highlight column A and right-click on it to select Fill With: Row Numbers.
  3. Highlight column B and right-click on it to select Set Column Values to open the Set Values dialog. Enter Col(B)=cos(Col(A)) in Before Formula Scripts and click the OK button to generate the data.

The following example will load a sample expression which contains multi-line scripts for normalizing data.

Example 3

  1. Create a new workbook.
  2. Highlight column A. Right-click on it and select Fill With: Row Numbers from the short-cut menu.
  3. Right-click on column A again and select Set Column Values from the short-cut menu to open the Set Values dialog.
  4. Select Formula: Load Samples: Normalize column 0 to 1 from the menu of the Set Values dialog to load scripts in the Before Formula Scripts. Then click the OK button to normalize the data.

Note: Each line of LabTalk script in the Before Formula Scripts panel should be terminated with a semicolon.


Example 4

  1. Create a new workbook.
  2. Click the Import Wizard button Button Import Wizard.png to open the dialog.
  3. Under Data Source, select the file \Samples\Graphing\Waterfall.dat. Click Finish to import the data.
  4. Activate the worksheet, click the Add New Columns button Button Add New Columns.png. Scroll to the end of the worksheet and note column DW(Y).
  5. Hightlight this column DW(Y), right-click and select Set Column Values from the short-cut menu to open the Set Values dialog.
  6. Input the following script into the Before Formula Scripts box.
  7. range r1 = col(DW);   //  Set range variable to the added column
    r1 = wcol(2);   // Set initial value of the range
    int nc = wks.ncols;   // Retrieve the total number of the columns
    
    // Loop over all y columns to sum them up
    for(page.v1=3; page.v1<= nc-1  ; page.v1+=1)
    {
      range rY = wcol(page.v1);
      r1 += rY;
    }
    
    r1 = r1/(nc-2); // Calculate the average of y columns
  8. Click OK to apply. The average of all y columns is calculated and input into column DW(Y).

Add Expressions in both Column Formula and Before Formula Scripts

Often, we need to enter scripts in both the Column Formula edit box and the Before Formula Scripts panel. For example, you can define range variables in Before Formula Scripts and then use them in Column Formula.

Example 5:

  1. Create a new project. There should be a empty workbook named "Book1".
  2. Highlight column A. Right-click and select Fill Column With: Row Numbers from the short-cut menu.
  3. Highlight column B. Right-click and select Fill Column With: Normal Random Numbers from the short-cut menu.
  4. Create another workbook named "Book2".
  5. Highlight column A in Book2. Right-click and select Fill Column With: Row Numbers.
  6. Highlight column B in this workbook. Right-click and select Set Column Values from the short-cut menu to open the Set Values dialog.
  7. Now we insert two range variables which point to the columns in Book1. Select Variables: Add Range Variables... from the menu of the Set Values dialog. The Range Browser appears. Choose two ranges in the dialog as the following screenshot shows:
    SetColumnValue2.png
    Click OK to return to the Set Values dialog.
  8. Enter r1+r2 into Column Formula. The Set Values dialog should look like:
    SetColumnValue3.png
  9. Click the OK button to generate data for column B. Each element in column B should be the sum of the elements in the corresponding row in Book1.
    SetColumnValue4.png

Example 6:

This is an example to show how to calculate the integral at each given T value using both integral function and User Defined function.

  1. Create a new Book.
  2. Set column A Long Name as T and set formula for Column A using the formula below:
    data(1,10,0.5)
  3. Set column B Long Name as Integral, highlight column B and right click to select Set Column Values to open dialog.
  4. Input integrand function as shown below in Before Formula Scripts and integral expression in Col(B)= edit box as shown below:
    SetFunValues5.png
  5. Click OK to apply. The calculated integral is shown below:
    SetFunValues6.png

The Before Formula Scripts is also provided below:

// Define Integrand
function double Myintg(double x, double T)
{
//Define constants
      Double h = 1.23*10^(-4); 
      Double k = 8.61*10^(-5);
 
      return 1/(exp(h*x/(k*T)) - 1);
}
 

© OriginLab Corporation. All rights reserved.