Users may have a varying number of rows and columns in a data file and need to refer to the last row, last column, or even last sheet in analysis. Sometimes we use 0 to refer to such dynamic ending, while in other places we use dend. We improved this in Origin 2021b so 0 will always be used. We also fixed the issue of results not updating when new columns are introduced in some cases.
Please download this zip file when following examples below.
Refer to Last Row
Column C contains a formula subtracting column B with the last value in column B. Save this worksheet as a worksheet template and import new data with different length into column A & B. The column C will update by subtracting new column B with new last point in column B.
Note:
- This also works in cell formula.
- Such excel like column notation isn’t supported in Labtalk script. You need to use col(C)=col(B)-col(B)[0] in Labtalk script.
Last Column in Calculation
To calculate rowwise sum, mean, and standard deviation of all columns from B to the end of a worksheet, in the past, user must specify the ending column name. In Origin 2021b, user can use 0 to refer to last column.
Note:
- 1! in sum() means 1st sheet.
- Besides sum_mean, sum_sd, we also support sum_min, sum_max, sum_n, sum_median.
- User can also use Statistcs: Descriptive Statistics: Statistics on Rows to achieve the same thing.
Graphing with Varying number of columns.
There are cases user wants to plot certain graph with multiple columns. User can specify 0 as dynamic end so when more columns are imported or calculated, the graph will auto update.
E.g. the following is a contour graph created from 2D binning result of data. Depending on the data, the number of columns in binning result sheet varies. When plotting it, user can specify ending column to be 0 ( there is context menu).
If user save such analysis template and import new data, the graph will update correctly.
To play with the example in zip file, open the opju file in zip file in Origin, go to 3. graphing_with_varying_number_of_columns folder. Click the yellow icon on 1st sheet and choose Import. U can see the graph updated.
If you click the icon and choose Source… Change the data to C:\Program Files\OriginLab\Origin2021b\Samples\Statistics\2D Binning 2.dat. The graph will be updated. Go to TwoDBin1 sheet, there are more columns.
Note: Before Origin 2021b, we use dend for such dynamic ending. But there are issues so when more data are introduced, it failed to auto update the graph.
Copy needed Columns to a new sheet for further analysis
This online COVID-19 data updates regularly with new date’s data appended at the end of the worksheet.
I am only interested in column G (Province_State) and data from column L to the last column. I can extract these columns by Copy Columns to feature in Origin. In the Source Column(s) node, I used 0 to specify the last column. Note: There is Dynmaic Ending context menu in Source Columns node.
Then I can do further analysis such as combine all states data into one row by Worksheet: Remove/Combine Duplicated Rows
After a few days, if i import the online data again, the result will be updated with new date’s data included.
To play with the example in zip file, open the opju file in zip file in Origin, go to 4. copy_needed_columns. The last column on 1st sheet is 3/16/2021. If you click the yellow icon and choose Import. New data is imported so there are more columns on 1st sheet. Sheet1 、 wdeldup sheets are also updated to include new columns.
Labtalk Examples
User can use 0 in range notation to refer to dynamic ending of row, column and sheet
- range r=0 //last column of active sheet
- range r=1!0 //last column of 1st sheet,
- range rsheet = 0! //last sheet
- 0!wcol(0)[0]=; //last cell in last column of last sheet or 0!col(0)[0]=;
- range rb=1:0 //first column to last column
- range -v r=1[1]:0[1] whole second row from 1st column to last column
- range r1 = [Book1]Sheet!1:0; //block from the 2nd column to the last column in Sheet1 of Book1. This is useful for virtual matrix. If more columns added, the r1 will dynamically update.
- range r2 =[Book1] Sheet1!2[2]:0[0]; // from 2nd row in 2nd column to last row of last column in Sheet1 of Book1
Use Layer -d 0 to delete last sheet in workbook.
Japanese 
English