2.65 FAQ-690 Two Quick Examples of Set Values dialog

Last Update: 11/4/2016

The Set Values dialog box is a versatile tool for performing math operations on worksheet or matrix datasets, defining variables or pre-processing of data with LabTalk Script. For a more in-depth discussion of this tool, start with these topics:

The following two quick examples will help you get started with the Set Values tool.

Note: A simplified "spreadsheet cell syntax" has been introduced that does not work in versions 2016 and earlier. The following two examples use the older syntax. To see how the first quick example works with the newer syntax, see Two Quick Examples.

Set Values for a Single Column

The following short tutorial will show you how to use this dialog to generate data for a simulated gaussian curve.

  1. Create a new workbook by clicking the New Workbook button New Workbook.png on the Standard toolbar.
  2. Highlight column A and right-click on it to select Set Column Values from the short-cut menu to open the Set Values dialog.
  3. Enter {-1:0.03:5} in the Column Formula edit box and then click the Apply button. Column A should be filled with a series of numbers.
  4. Then we will use the one of the navigation buttons to let you input an expression for the second column without closing the dialog. Click the Next button Next Button.png. You will see that column B is highlighted in the worksheet.
  5. Now enter 1 + (5/(1.5*sqrt(PI/2)))*exp(-2*((col(a)-2)/1.5)^2) in the Column Formula edit box and choose Auto in the Recalculate drop-down list.
  6. Click the OK button. The Set Values dialog is closed. You will see the results in the following worksheet.
    (If you create a line graph of column B, you will get a graph similar to the one next to the workbook.)
    Quick Start SetColumnValues 1.png
  7. Double click on the F(X) column label row of column B, and change the formula to 1+(5/(1.5*sqrt(PI/2)))*exp(-2*((col(a)-2)/1.5)*2). You will find the value in the column B is updated automatically.
    (Click the Rescale Button Rescale.png button, the graph is also updated.)
    Quick Start SetColumnValues 2.png

Origin supports using a sub-range of a column as function argument in the Set Values dialog.

For example: To calculate the sum of a sub-range from i-3 to i+3 in column A (where " i " is the row index), you can enter a formula in the Column Formula edit box.

Total(col(A)[i-3:i+3])
Note: When using i and j in Set Values or in the F(x)= column label row, you must refer to them using lowercase letters. Uppercase I and J will be interpreted as worksheet column short names.

Set Values for Multiple Columns

The following short tutorial will show you how to use this dialog to set values for multiple columns simultaneously.

  1. Create a new project by clicking the New Project button Button New Project.png on the Standard toolbar.
  2. Click the Import Multiple ASCII button Button Import Multiple ASCII.png to import the files F1,dat and F2.dat in the <Origin Folder>\Samples\Import and Export\ path. In the impASC dialog, select Start New Books for the Import Mode drop-down list.
  3. Two workbooks will be created, named as F1 and F2. Click the New Workbook button New Workbook.png on the Standard toolbar to create another workbook.
  4. With the 3rd workbook active, click Add New Columns button Button Add New Columns.png to add a column. Highlight all columns, select Column: Set Multiple columns values from the main menu or right-click the columns to select Set Multiple columns values in the contaxt menu to open the Set Values dialog.
  5. Expand the bottom panel by clicking the Show Scripts button Button Show Scripts.png. Enter below scripts in the Before Formula Scripts edit box,
    range r1=[F1]F1!wcol(j); //"j" is the column index.
    range r2=[F2]F2!wcol(j);
  6. Enter (r1+r2)/2 in the Column Formula edit box
  7. Select Options: Direct Edit Formula Cell to uncheck the option.
  8. Select Options: Formula Text... and enter (F1+F2)/2 in the Formula Text dialog, then click OK button.
  9. Click the OK button in the Set Values dialog. You will see the results in the worksheet, and (F1+F2)/2 will display in the F(x) column label row instead of the formula.

Minimum Origin Version Required: 9.1 SR0


Keywords:Set Values Dialog