OriginLab Corporation - Scientific Graphing and Data Analysis Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis

Entering Expressions in the Set Column Dialog


Contents


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:

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

Image:SetValuesPanelNew.png

The following are some useful tips for inputting expressions.

Image:Tip_icon.png
  1. The Variables menu can help you define variables used in the expression.
  2. Origin offers lots of built-in functions under the F(x) menu, which can be directly used in the expression. You can also create a User-Defined Function yourself.
  3. The letter i is a system variable that stores the current row index. If you want to fill the column with row numbers, you can directly input i in Column Formula.
  4. 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.
  5. The {} notation can be used in Column Formula to fill the selected column with a series of equi-spaced numbers.
    • {begin:step:end} fills the column with numbers beginning at begin, ending at end and incrementing by step.
    • {begin:end} fills the column with numbers beginning at begin, ending at end and incrementing by 1.
  6. Columns from other books and sheets can be accessed by either defining a range variable in the Before Formula Scripts panel using the Variables menu, or by directly typing LabTalk substitution notation in the formula edit box.

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 function and user-defined function. The following is an example that 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 F(x): 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, loop 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 the LabTalk scripts in the Before Formula Scripts panel should be ended with a semicolon.

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 4:

  1. Create a new project. There should be a empty workbook named "Book1". We will fill some numbers in it and use the numbers later.
  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. It should be named "Book2"
  5. Highlight column A in Book2. Right-click and select 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: Insert 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:
    Image:SertColumnValuesInsertRangeNew.png
    Click OK to return to the Set Values dialog.
  8. Enter r1+r2 into Column Formula. The Set Values dialog should look like:
    Image:SetValuesInputValues.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.
    Image:GenerateDataWorksheetNew.png