| Author |
 Topic  |
|
|
FdThornton
3 Posts |
 Posted - 02/05/2014 : 6:58:31 PM
|
Origin Ver. and Service Release (Select Help-->About Origin): 8.6, Pro
Operating System: Windows 7
Dear Forum,
I have a worksheet with 100 Y-value columns and 1 X-value column. I want to multiply all columns each with the X-column and then sum the result up and then divide this value by the sum of the X-column (basically calculating the average frequency of a spectrum). The results should be in a new column, so that I end up with a new column with 100 rows.
The only idea I had is the script below:
i1 = 1
i2 = 200
range qq = (col(b)*col(a))[i1:i2]
sum(qq)
sumA = sum.total
range ww = col(a)[i1:i2]
sum(ww)
sumB = sum.total
col(e1)[1] = sumA/sumB
which is fine, but I need a loop or something as I don't want to run this script a 100 times...
Can anyone help me please?
Thank you very much in advance!!!! |
|
|
lkb0221
China
497 Posts |
Posted - 02/06/2014 : 09:48:23 AM
|
Hi,
// Assume your worksheet is XYYYY
range r1 = col(1); // r1 point to the first column in the worksheet
double sumX = total(r1); // Calculate the sum of your X column
wks.addcol(); // Add a new column to worksheet
col($(wks.ncols))[L]$ = "average frequency"; // Change column long name
dataset dd; // Define a loose dataset for calculation
loop(ii,2,wks.ncols-1) // loop among all Y columns
{
range r2 = col($(ii));
dd = r2 * r1;
double sumY = total(dd);
col("average frequency")[ii-1] = sumY / sumX;
}
//Script end
Zheng
OriginLab |
Edited by - lkb0221 on 02/06/2014 10:31:06 AM |
 |
|
|
FdThornton
3 Posts |
Posted - 02/06/2014 : 9:47:01 PM
|
Dear Zheng,
thank you so much for your help!!
Somehow, when running your script, I get the error message that [ii-1] is a wrong index.
Can yo uhelp me once again with this error message?
Thank you very much.
|
|
|
|
lkb0221
China
497 Posts |
Posted - 02/07/2014 : 11:11:34 AM
|
Hi,
That's strange... I cannot reproduce this error message...
Anyway, I changed the script a little to see if it can avoid this error in your side.
//Script Start
range r1 = col(1);
double sumX = total(r1);
wks.addcol();
col($(wks.ncols))[L]$ = "average frequency";
col("average frequency")[wks.nrows]=1/0; // put missing values in result column to avoid some possible error
dataset dd;
int tt=1; // Create a new int variable to point to the row number of result column
loop(ii,2,wks.ncols-1)
{
range r2 = col($(ii));
dd = r2 * r1;
double sumY = total(dd);
col("average frequency")[$(tt)] = sumY / sumX; // Put value to result column
tt++; // Move pointer down by one
}
//Script end
Zheng
OriginLab |
Edited by - lkb0221 on 02/07/2014 12:04:51 PM |
|
|
|
FdThornton
3 Posts |
Posted - 02/11/2014 : 7:55:38 PM
|
Dear Zheng,
thanks again for your help. And sorry that I could not come back to the forums earlier.
I really do not understand why it still does not work for me.
I get a new column, I get the new long name. This time I had no error message but only a zero in the forst cell of the new column and then only two hyphens in each of the following cells.
Probably, I am doing something wrong?
I just copy pasted your script, deleted the semicolons and the comments and then pressed enter where the semicolons were.
Thank you so much for taking your time!! |
|
|
|
lkb0221
China
497 Posts |
Posted - 02/12/2014 : 09:46:30 AM
|
Hi,
I guess your worksheet structure might not be the same as what my script is based on. Please see the screenshot below:
My script is based on a pure XYYY mode worksheet.
Activate Book1, highlight all lines in Script Window and press Enter, you should get Book2.
If your worksheet is different, please post a screenshot, or, directly send your opj file to tech@originlab.com .
Zheng
OriginLab |
|
|
| |
Topic |
|
|
|
Calculation with columns