Origin supports import metadata into column label rows, such as Long name, Units, Comments or even Parameter row(s). User can then use them in column calculation. In this blog, we will show how to do it starting from simple case and ending with a complicated case.
Simple Case
In this example, the x and y correction factors are imported into the UserParameter0 row of the each column. We would like to add them into the raw data to get corrected values.
You can download the file DOC-2307_Store_Vars_in_OPJ.ZIP and open the OPJ file in Origin. Go to Part1->Simple Case folder in Project Explorer. You should see Book3.
Use the value directly in Formula Box
1. Add two new columns:C and D. Set column C as X.
2. Right-click column C and choose Set Column Values… to open the Set Values dialog. Enter the following in Col(C)= Formula box
Explanation of the syntax.
- Col(a)[UserParameter0]$ – refers to the UserParameter0 of column A as a string. The syntax is col(ColumnName)[ColumnLabelChar]$. For user-defined parameter row, you can use either the real parameter name or Dn. Read this page about how to access Column label rows in LabTalk script.
- %(col(a)[UserParameter0]$) converts number string to a numeric value. So does the built-in function value(), e.g. value(col(a)[UserParameter0]$).
3. Click OK. Column C will be filled with column A plus 5.30.
Define Variable in Before Formula Scripts First
You can also define a double variable in Before Formula Scripts to refer to the UserParameter0 and then use it in Col(C)= Formula box to make the formula more reasonable.
GUI way to define variable
If you don’t want to type in Before Formula Scripts to define the variable, there is also GUI way to define variables for column label cell/
1. Choose Variables: Add Column Label Cell by Selection menu in Set Values dialog. You will see the Select From Worksheet box below.
2. Click on 5.30 cell and then click the button in Select from Worksheet box. You will be sent back to Set Values dialog. And a variable is defined in Before Formula Scripts. You can change the variable name to xcorr. Also you may notice the syntax used here is col(A)[D1]$. It the same as col(A)[UserParameter0]. D1 refers to 1st User-defined Parameter.
3. You can also add scripts in Before Formula Scripts to rename long name and units of column C. Here col(C)[L]$ refers to long name. col(C)[U]$ refers to units.
Repeat the process to set column D values based on column B and its UserParameter0 cell.
You can go to Part1 -> Results subfolder in Project Explorer. See the expected result in Book1 – Simple Case. To see the formula, click the green lock icon and choose Change Parameters….
Complicated Case
In Book4 ofPart1 -> Complicated Case subfolder of the project, all header lines of a data file was imported into the Comments cell in column A. X Corr: 5.30 and Y Corr: -152.68. are correction factors. In such case, we need to extract 5.30 and -152.68 first before we could use them in column calculation.
Here is what we can enter to Set Values dialog of Column C to get corrected values.
Basically in Before Formula Scripts box, we defined a double variable called xcorr. We used several built-in LabTalk string functions to extract 5.30 from Comments cell of column A, built-in value() function to convert it to a number. Then we added xcorr to each value in column A to get column C values.
Here is detailed information of the functions we used. You can actually double click function name in Set Values dialog in Origin 2015 to get syntax help.
- col(a)[C]$ refers to the Comments cell in column A.
- matchend(col(a)[C]$, “X Corr: “) searches a string — in our case, the entire contents of the Comments cell in column A — and returns an integer corresponding to the position of the last character (a space) in the string “X Corr: “. That integer is 173. So, this part of our expression evaluates to 173, and we pass that value to the containing function mid().
- mid(col(a)[C]$, 173)$ takes a string (again, the entire contents of the Comments cell in Column A ) and, unless we specify otherwise, returns the remainder of the string beginning at position 173. So, what is returned looks like this …
5.30
Y Label: Intensity
Y Unit: arb. unit
Y Corr: -152.68
>>>>>Begin Raw Data<<<<<
… and this is passed to the containing token function.
- token(“remainder of string”, 1) takes the remainder of our string and returns the first token, or 5.30. Though this looks like a numeric value, it is just part of our Comments string, so in the final step, we convert string ” 5.30″ to the numeric value 5.30.
- value(” 5.30″) returns 5.30.
Repeat the above process with column D, defining and using variable ycorr, and add it to each value of column B to get Column D values.
You can go to Part1 -> Results subfolder in Project Explorer. See the expected result in Book2 – Complicated Case. To see the formula, click the green lock icon and choose Change Parameters….
In Use Metadata in Worksheet in Calculation – Part2: Page.Info blog, you will see the case of using variables or stored header file information in workbook page.info. in column calculation.
Thanks for reading. I hope that this has been helpful.
If you would like to read more about some of the features and topics covered in this post, see the following pages in our online documentation:
English 
Japanese