• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to put a condition based on the below mentioned query?

Dear Excel Experts,

In the attached working file i have some trades which went on the 5 min, 15 min and 1 hr. This are represented in ND ( No Direction), Buy and Sell. In the Mood starting from B3 i need to give some conditions based on the criteria mentioned below:

1. If 1 Buy and 3 Sell shown then the mood is "BUY"
2. If 1 Sell and 3 BUY shown then the mood is "SELL"
3. If all its showing SELL then the mood is "SELL"
4. If all its showing BUY then the mood is "BUY"
5. if 1 ND and rest 3 BUY then the mood is "BUY"
6 if 1 ND and rest 3 SELL then the mood is "SELL"
7. If 2 ND and 2 BUY then the mood is "WAIT"
8.If 2 ND and 2 SELL then the mood is "WAIT"

Hoping somebody from the forum can help me to solve the above query.

Thanks % Regards,

Sonjoe
 

Attachments

ETAF

Member
how about
=IF(OR(AND(COUNTIF(C3:F3,"Buy")=1,COUNTIF(C3:F3,"Sell")=3), COUNTIF(C3:F3,"Buy")=4,AND(COUNTIF(C3:F3,"ND")=1,COUNTIF(C3:F3,"BUY")=3)),"BUY",IF(OR(AND(COUNTIF(C3:F3,"Buy")=3,COUNTIF(C3:F3,"Sell")=1),COUNTIF(C3:F3,"Sell")=4,AND(COUNTIF(C3:F3,"ND")=1,COUNTIF(C3:F3,"Sell")=3)),"SELL",IF(OR(AND(COUNTIF(C3:F3,"ND")=2,COUNTIF(C3:F3,"BUY")=2),AND(COUNTIF(C3:F3,"ND")=2,COUNTIF(C3:F3,"Sell")=2)),"WAIT","No Combination")))

BUT you have other combinations
Like row 20, 23, 30 etc
 

Attachments

bosco_yip

Excel Ninja
Your "Lookup Table" appear not completely, try to use Helper Columns solution as in

1] Complete your "Helper Lookup Table (Column L to Column P)" if you find the wording of "Not in list" in your result column (Column B)

2] My set up is simple and the result column using Vlookup function as following

3] B3, formula copied down:

=IFERROR(VLOOKUP(J3,O$3: P$100,2,0),"Not in List")

4] Please see attachment for the other helper column formula and hide the helper columns as per your necessary.

81032
 

Attachments

Last edited:
Your "Lookup Table" appear not completely, try to use Helper Columns solution as in

1] Complete your "Helper Lookup Table (Column L to Column P)" if you find the wording of "Not in list" in your result column (Column B)

2] My set up is simple and the result column using Vlookup function as following

3] B3, formula copied down:

=IFERROR(VLOOKUP(J3,O$3: P$100,2,0),"Not in List")

4] Please see attachment for the other helper column formula and hide the helper columns as per your necessary.

View attachment 81032
Just checked bosco its just working awesome. This is the way i wanted and u have done it neat. Kudos Bosco
 
Top