| T O P I C R E V I E W |
| g.sebastian74@yahoo.fr |
Posted - 01/15/2025 : 06:41:35 AM
OriginPro 2024b 10.1.5.132 Government (64-bit)
Operating System: Windows 10
Hi dear Experts,
I want to compare data in column A to Column D. If (Aj -Di) is positive and less than 1E8, report the difference in cell G corresponding to A. If not, no data is reported.
Example.
A1 is compared to D and found that A1-D15 fulfills the requirement 0<A1-D15<1E8. Report A1-D15 in cell G1
A2 is compared to D (starting from D16) and found that A2-D22 fulfills the requirement 0<A2-D22<1E8. Report A2-D22 in cell G2
A3 is compared to D and found no case that fulfills the requirement 0<A3-D23:D1000<1E8. Report "" (nothing) in cell G3
A4 is compared to D and found no case that fulfills the requirement 0<A4-D23:D1000<1E8. Report "" (nothing) in cell G4
...
A8 is compared to D (starting from D23) and found that A8-D74 fulfills the requirement 0<A8-D74<1E8. Report A8-D74 in cell G8
A9 is compared to D (starting from D75) and found that A9-D89 fulfills the requirement 0<A9-D89<1E8. Report A9-D89 in cell G9
…..
What function combination do I have to input in G to get the expected result?
NB: Cell G is filled only up to G9.
Thank you for your support
guembou |
| 7 L A T E S T R E P L I E S (Newest First) |
| YimingChen |
Posted - 01/17/2025 : 1:42:22 PM
Please check the attached project file. Since your dataset is large, we can calculate in two steps.
1. In Col(G), use the table function to find the smallest value in col(D) that is larger than col(A)[i].
2. In Col(H), test if the difference Col(A)[i] - Col(G)[i] is within (0, 1E8).
And the project
https://my.originlab.com/ftp/forum_and_kbase/Images/testExample2.opju
James |
| g.sebastian74@yahoo.fr |
Posted - 01/17/2025 : 05:16:23 AM
quote:
Originally posted by YimingChen
Is your col(A) and col(D) values monotonically increasing? If so, we can use the table() function in Origin to find the first value in Col(D) that is larger than col(A)[i].
Hi James,
Thank you for your suggestion.
My Col(A) and Col(D) values monotonically increase and the dimension of Col(D) is higher than Col(A) dimension.
Here is a sample of my table.
https://my.originlab.com/ftp/forum_and_kbase/Images/testCebastien2.ogwu
I tried the following:
table(col(A), col(A), Col(D)[i],2)-D>0 && table(col(A), col(A), Col(D)[i],2)-D<1E8? table(col(A), col(A), Col(D)[i],2) - D : 0/0
How can I get the answer displayed to the G cell corresponding to A, not D.
For example A1-D15 to G1; A2-D22 to G2, ...
Thanks
guembou |
| YimingChen |
Posted - 01/16/2025 : 4:55:42 PM
Is your col(A) and col(D) values monotonically increasing? If so, we can use the table() function in Origin to find the first value in Col(D) that is larger than col(A)[i]. See below:
I attached the sample project as well.
https://my.originlab.com/ftp/forum_and_kbase/Images/testExample.opju
James |
| g.sebastian74@yahoo.fr |
Posted - 01/16/2025 : 05:27:18 AM
Dear Snow,
See below what I obtained.
Your formula always checks A[i] and D[i+14]. For example, it checks A1 &D15, A2&D16, A3&D17, ... etc. That is not what I want.
My problem again states that I need to check A1 and B1, B2, ... Bn, and when a condition is fulfilled (in this case A1 -B15), fill the cell G1 with the value A1-D15 and move to A2. Compare A2 with D16, D17, ... Dn' and when a condition is fulfilled (in this case A2 and D22), fill the cell G2 with the value A2-D22 and move to A3. Compare A3 with D23, D24, ... Dn" and when a condition is fulfilled (in this case A2 and D34), fill the cell G3 with the value A3-D34 and move to A4. ... etc.
With your proposed formula, I didn't get anything else except the G1 value.
If I use Excel with the following formula, I will obtain the desired result. The issue is that I can no longer use Excel at my convenience and I'm transitioning to Origin.
=IF(SUMPRODUCT((A1-D$1:D$1048576>0)*(A1-D$1:D$1048576<1E8)*(A1-D$1:D$1048576))>0,SUMPRODUCT((A1-D$1:D$1048576>0)*(A1-D$1:D$1048576<1E8)*(A1-D$1:D$1048576)),"")
Thank you for your support!
Does anyone else understand my issue? Maybe I'm explaining it in a wrong way.
guembou |
| snowli |
Posted - 01/16/2025 : 12:35:52 AM
I tried it working.
The if function i wrote checkes the condition (1st argeument) is true or not.
A[i]-D[i+14]>0 && A[i]-D[i+14]<1E8
If true, then A[i]-D[i+14] will be assigned in corresponding ith row in G.
Else, blank value assigned.
Please share your opju with not working formula.
Thanks, Snow
|
| g.sebastian74@yahoo.fr |
Posted - 01/15/2025 : 11:44:02 PM
quote:
Originally posted by snowli
Hello,
There is if( ) function similar to Excel in Origin. https://www.originlab.com/doc/LabTalk/ref/If-func
And u can use [] to specify row index.
i refers to current row
E.g. enter the following in Fx cell of Column G
if(A[i]-D[i+14]>0 && A[i]-D[i+14]<1E8, A[i]-D[i+14], " ")
We also support cell formula
E.g. in G1 cell, you can enter =if(A1-D15>0 && A1-D15<1E8, A1-D15, " ")
Press Enter to see the result. Then drag the tiny dot at bottom right corner of G1, drag downwards to get such formula with row incremented.
Thanks, Snow
Hello,
The proposed solution is not for the problem I want to solve.
Ai value is compared to several Dj values and reports the difference when condition match and nothing if not.
Thank you!
guembou |
| snowli |
Posted - 01/15/2025 : 7:19:35 PM
Hello,
There is if( ) function similar to Excel in Origin. https://www.originlab.com/doc/LabTalk/ref/If-func
And u can use [] to specify row index.
i refers to current row
E.g. enter the following in Fx cell of Column G
if(A[i]-D[i+14]>0 && A[i]-D[i+14]<1E8, A[i]-D[i+14], " ")
We also support cell formula
E.g. in G1 cell, you can enter =if(A1-D15>0 && A1-D15<1E8, A1-D15, " ")
Press Enter to see the result. Then drag the tiny dot at bottom right corner of G1, drag downwards to get such formula with row incremented.
Thanks, Snow |
|
|
