| Author |
 Topic  |
|
|
LordM00
Australia
5 Posts |
 Posted - 08/12/2021 : 11:46:04 PM
|
Origin Ver. and Service Release (Select Help-->About Origin): OriginPro 2020b (64-bit) 9.7.5.184
Operating System: Windows 10
Hello,
I am trying to find the maximum (and minimum in some cases) value in a column, along with any other corresponding values in the same row, e.g. For the maximum x value I would also want the y value where this occurs.
This can be done quite easily using a Worksheet Query of 'If A=Max(col(A))', which will either highlight the data or output it to a new workbook. The trouble is that it will do this only for one given worksheet, and I have a lot more than that. Doing this for each individual worksheet will take a very long time. Is there any way to have a worksheet query apply to multiple sheets at once?
I can also use Descriptive statistics on columns to easily get all of the values that I need, since I can select multiple worksheets for the input data. Unfortunately, this only gives me the maximum value from e.g. Col A, but not the corresponding value in Col B. Is there any way to make this give my both values for each sheet?
I have included an example project file of what I want to achieve, as well as some images.
Thank you,
Michael
https://my.originlab.com/ftp/forum_and_kbase/Images/Max%20value%20example.opju |
|
|
snowli
USA
1386 Posts |
Posted - 08/13/2021 : 2:06:06 PM
|
Hello,
Instead of using Batch Processing method, u can define function and use it in Set Column Values
For Value1 column, I defined an AMax() function and used it in Set Column Values dialog.
function int AMax(int nSheet) {
range ra = [Book1]$(nSheet)!col(A);
return Max(ra)
}
For Value2 column, I defined an AMaxBVal() function and used it
function double AMaxBVal(int nSheet) {
range ra = [Book1]$(nSheet)!col(A);
range rb = [Book1]$(nSheet)!col(B);
sum(rb);
return rb[sum.iMax];
}
Thanks, Snow |
 |
|
|
LordM00
Australia
5 Posts |
Posted - 08/13/2021 : 9:37:21 PM
|
Hi Snow,
This is great.
Thank you for the help,
Michael |
|
|
|
LordM00
Australia
5 Posts |
Posted - 08/14/2021 : 07:46:06 AM
|
quote:
Originally posted by snowli
Hello,
Instead of using Batch Processing method, u can define function and use it in Set Column Values
For Value1 column, I defined an AMax() function and used it in Set Column Values dialog.
function int AMax(int nSheet) {
range ra = [Book1]$(nSheet)!col(A);
return Max(ra)
}
For Value2 column, I defined an AMaxBVal() function and used it
function double AMaxBVal(int nSheet) {
range ra = [Book1]$(nSheet)!col(A);
range rb = [Book1]$(nSheet)!col(B);
sum(rb);
return rb[sum.iMax];
}
Thanks, Snow
Hi Snow,
I have a couple of questions/comments about these functions that I hope that you can help me with.
1. My data is not always in the form of integers, so I changed the first line to function double AMax(int nSheet){. Hopefully if anyone else has the same question as me they keep in mind that this may be necessary.
2. How does second function, AMaxBVal, work? I need it to give the the value of B when A is at its maximum, but instead it seems to be giving me the maximum value of B, and I am sure that these are not the same thing. Maybe I'm not reading the code right, but should it be Sum(ra) instead of rb? Labview's sum function confuses me, so I'm not too sure, but it seems to work after making that change. I think its a typo, but thought it best to ask just in case.
I don't really need any more help, since I have the data I need now, but if I've made a horrible mistake, please let me know.
Thank you,
Michael |
|
|
|
snowli
USA
1386 Posts |
Posted - 08/16/2021 : 11:57:46 AM
|
Hi Michael,
You are right. It should be sum(ra);
after running sum(ra)
It generates many stats of range ra.
E.g. u can run
sum.= to see all quantities
sum.imax gives the row index of max value in ra.
Then rb[sum.iMax] returns the value in rb with that row index
Sorry for the mistake in code.
Best, Snow |
|
|
|
LordM00
Australia
5 Posts |
Posted - 08/16/2021 : 8:41:33 PM
|
Hi Snow,
I see. It seems that Sum is very useful! Thanks for the help.
Michael |
|
|
| |
Topic |
|
|
|
max x column value and its y value multiple sheets