Get the values based on certain criteria
My input range is V2:Y4; AA2:AC4; AG2:AI4 which contains numerical values.
Starting with column W, 1 cell i.e. either of W2 or W4 is equal to 1 of the cells from the preceding column.
Just for the sake of communication, we will call this equal cell as OPEN and the other cell as CLOSE.
Output range: W10:Y10; AA10:AC10; AG10:AI10
Output required as: 100 if OPEN < CLOSE; 200 if OPEN > CLOSE; 0 if OPEN = CLOSE.
How to accomplish?
try this:
Please Login or Register to view this content.
As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.
And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]
Your formula (changed to W instead of V) =IF(W2<W4,200,IF(W2>W4,100,0)) would have been correct only if W2 is equal to either of V2 or V4.
In my example, column Y (Y2=552 & Y4=433), your formula will give Y10=100 (incorrect). Correct answer is Y10=200 since Y2 is equal to X2 (either of X2 OR X4) AND Y2 > Y4.
In my example, column X (X2=552 & X4=512), here X4 is equal to W2 (either of W2 OR W4) and since X4 < X2, X10=100
How to accomplish?
Based on what you have said, does this get you any closer?
=IF(AND(OR(W2=V2,W2=V4),W2>W4),200,100)
I feel there is still something missing in your explanation.
Last edited by AliGW; 05-22-2022 at 01:39 AM. Reason: Removed example.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I think this is it:
=IF(AND(OR(W2=V2,W2=V4),W2>W4),200,IF(W2=W4,0,100))
Your formula is giving the desired results. But why the formula does not checks whether the constraint is equal to 1 of the 2 numbers of the preceding column?
If the constraint is not equal to either of the 2 numbers (of the preceding column), the output value=0. Please see the attached file.
Some modification in the formula is requested.
I don't understand, sorry. The formula DOES check that:
OR(W2=V2,W2=V4)
Your new requirements seem to be adding inconsistencies that I don't follow.
Last edited by AliGW; 05-22-2022 at 02:27 AM.
Instead of shifting goalposts, please tell us:
1. What criteria are required for 200?
2. What criteria are required for 100?
3. What criteria are required for 0?
Tell us EVERYTHING, please.
I am very sorry. Your formula does checks with the 2 numbers of the preceding column. It was my mistake in analyzing your formula.
What I finally need is: if the cell value which gets checked with the 2 cell’s value of the preceding column, does not match then output cell=0.
In my example:
AA2=451 & AA4=433
AB2=382 which neither equal to AA2 nor equal to AA4. Therefore AB=0
Please some modification in the formula is requested.
So why is AA not 0? It doesn't meet the criteria, either.
And what are the exact criteria for 100?
Please answer the questions I have asked directly.
I've run out of time - sorry.Please some modification in the formula is requested.
Last edited by AliGW; 05-22-2022 at 02:43 AM.
1. What criteria are required for 200? Your formula takes care of 200.
2. What criteria are required for 100? Your formula takes care of 100.
3. What criteria are required for 0?
Condition 1: If NEITHER W2 is equal to V2 OR V4 NOR W4 is equal to V2 OR V4;
Condition 2: W2=W4
Condition 3: Either of W2 OR W4=0
If either of any 3 conditions are met than corresponding column’s cell value =0 (in row 10).
From the description in post #11 it seems that the formula for W10 could be:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks JeteMc. It works now.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks