Need Help with Multiple IF in One Cell

[intradayteam]

I worked out a formula

If A2 is "BUY" then it uses below formula

=IF(ISBLANK(G2),"",IF(G2>F2,"Rocked",IF(AND(G2<=F2,G2>=E2),"Tgt Done",IF(AND(G2<E2,G2>C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2<C2,G2>D2),"Exit in Loss",IF(G2<=D2,"SL Taken","")))))))

and IF A2 is "SELL" then it uses below formula

=IF(ISBLANK(G2),"",IF(G2<F2,"Rocked",IF(AND(G2>=F2,G2<=E2),"Tgt  Done",IF(AND(G2>E2,G2<C2),"Small Profit",IF(G2=C2,"Exit at  Cost",IF(AND(G2>C2,G2<D2),"Exit in Loss",IF(G2>=D2,"SL  Taken","")))))))

I want both formulas combined in one cell. I am worried about file size as my sheet has huge data in it. I am not in favour of using VBA as i dont have much knowledge about that.

As far as i searched, Maximum 7 IFs are allowed in each cell formula.

Pls help. If possible try to solve the issue with Formula only (no vba).

Thanks a lot.

 

[Gerald Higgins]

Hi, welcome to the board.

These IF statements look confusing - is there something missing from them ?
For example, in the first one, what do these bits mean
and(g2c2)
and(g2d2)

In the second one, this bit doesn't make sense
IF(G2=F2,G2<=E2)

Why don't you describe in words exactly what you want to do, this may make it easier for people to suggest a solution.

 

[intradayteam]

am sorry that i did typo mistake.

If A2 is "BUY" then it uses below formula

=IF(ISBLANK(G2),"",IF(G2>F2,"Rocked",IF(AND(G2<=F2,G2>=E2),"Tgt Done",IF(AND(G2<E2,G2>C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2<C2,G2>D2),"Exit in Loss",IF(G2<=D2,"SL Taken","")))))))

and if A2 is "Sell" then it uses below formula

=IF(ISBLANK(G2),"",IF(G2<F2,"Rocked",IF(AND(G2>=F2,G2<=E2),"Tgt Done",IF(AND(G2>E2,G2<C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2>C2,G2<D2),"Exit in Loss",IF(G2>=D2,"SL Taken","")))))))

Let me explain in Details.

Say, A coloum has value of either "BUY" or "SELL"

3 criterias are there

1. If G2 is blank then H2 must remain Blank.

2. If a1 is "BUY" :
then if G2>F2, Result would be "Rocked"
IF G2<=F2 and G2>=E2, Result would be "Tgt Done"
IF G2<E2 and G2>C2, Result would be "Small Profit"
IF G2=C2,Result would be "Exit at Cost"
IF G2<C2 and G2>D2), Result would be "Exit in Loss"
IF G2<=D2,Result would be "SL Taken",

3. Same but reverse formula applies, if a1 is "SELL"
then if G2<F2, Result would be "Rocked"
IF G2>=F2 and G2<=E2, Result would be "Tgt Done"
IF G2>E2 and G2<C2, Result would be "Small Profit"
IF G2=C2,Result would be "Exit at Cost"
IF G2>C2 and G2<D2), Result would be "Exit in Loss"
IF G2>=D2,Result would be "SL Taken",

I want all the three conditions in Cell H2 (as shown in picture)

For your information, this is a sheet to track stock performance.
In simple words,If I buy some stock &

If booked price is more than entry price and less than tgt1, than its Small Profit
If booked price is equal to or more than TGT1, than its Tgt Done
If booked price is between TGT1 & TGT2 or equal to TGT2, then its TGT done.
If booked price is more than TGT2, then Rocked.
If booked price is equal to entry price then Exit at cost
If booked price is Less than Entry Price but More than Stoploss then Exit in Loss
If booked price is Less than or equal to Stoploss than SL Taken


In the same way, if i sell some stock &
If booked price is less than entry price and more than tgt1, than its Small Profit
If booked price is equal to or less than TGT1, than its Tgt Done
If booked price is between TGT1 & TGT2 or equal to TGT2, then its TGT done.
If booked price is less than TGT2, then Rocked.
If booked price is equal to entry price then Exit at cost
If booked price is more than Entry Price but less than Stoploss then Exit in Loss
If booked price is more than or equal to Stoploss than SL Taken


I hope you guys understand the whole structure.

 

[intradayteam]

<e2,g2><c2,g2><f2,"rocked",if(and(g2><c2),"small profit="" ,if(g2="C2,"Exit" at="" cost="" ,if(and(g2=""><d2),"exit in="" loss="" ,if(g2="">I dont know why but everytime i type full excel formula here, some of > or < gets disappears as soon as i post it. I tried posting several times but no success.

so i am uploading codes in TXT file uploaded at google pages. Have a look

http://sites.google.com/site/intradayteam/temp.txt?attredirects=0&d=1


Let me explain in Details here. Pls look at txt file first.

Say, A coloum has value of either "BUY" or "SELL"

3 criterias are there

1. If G2 is blank then H2 must remain Blank.

2. If a1 is "BUY" :
then if G2>F2, Result would be "Rocked"
IF G2<=F2 and G2>=E2, Result would be "Tgt Done"
IF G2<e2 and="" g2="">C2, Result would be "Small Profit"
IF G2=C2,Result would be "Exit at Cost"
IF G2<c2 and="" g2="">D2), Result would be "Exit in Loss"
IF G2<=D2,Result would be "SL Taken",

3. Same but reverse formula applies, if a1 is "SELL"
then if G2<f2, result="" would="" be="" rocked="">
IF G2>=F2 and G2<=E2, Result would be "Tgt Done"
IF G2>E2 and G2<c2, result="" would="" be="" small="" profit="">
IF G2=C2,Result would be "Exit at Cost"
IF G2>C2 and G2<d2), result="" would="" be="" exit="" in="" loss="">
IF G2>=D2,Result would be "SL Taken",

I want all the three conditions in Cell H2 (as shown in picture)

For your information, this is a sheet to track stock performance.
In simple words,If I buy some stock &

If booked price is more than entry price and less than tgt1, than its Small Profit
If booked price is equal to or more than TGT1, than its Tgt Done
If booked price is between TGT1 & TGT2 or equal to TGT2, then its TGT done.
If booked price is more than TGT2, then Rocked.
If booked price is equal to entry price then Exit at cost
If booked price is Less than Entry Price but More than Stoploss then Exit in Loss
If booked price is Less than or equal to Stoploss than SL Taken


In the same way, if i sell some stock &
If booked price is less than entry price and more than tgt1, than its Small Profit
If booked price is equal to or less than TGT1, than its Tgt Done
If booked price is between TGT1 & TGT2 or equal to TGT2, then its TGT done.
If booked price is less than TGT2, then Rocked.
If booked price is equal to entry price then Exit at cost
If booked price is more than Entry Price but less than Stoploss then Exit in Loss
If booked price is more than or equal to Stoploss than SL Taken


I hope you guys understand the whole structure.</d2),></c2,></f2,></c2></e2></d2),"exit></c2),"small></f2,"rocked",if(and(g2></c2,g2></e2,g2>

 

[Gerald Higgins]

The missing < and > symbols are a recognised bug on this board.
Try leaving a space symbol after each < or >.

 

[intradayteam]

Yeah, next time i will use spaces.

Pls download TXT file attacthed in post, Its a Direct Link, No need of waiting.

Thanks

 

[Gerald Higgins]

Nothing personal you understand, but I'm not downloading anything

Can you post again with the < and > symbols ?
I've tried working out what you mean, but it's just taking too long.

 

[intradayteam]

I dont understand why its not valid to post TXT file.

Anyways.. here is the code, this time i kept spaces around > and < symbols

Hope it works.

If A2 is "BUY" then it uses below formula

=IF(ISBLANK(G2),"",IF(G2 > F2,"Rocked",IF(AND(G2 < =F2,G2 > =E2),"Tgt Done",IF(AND(G2 < E2,G2 > C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2 < C2,G2 > D2),"Exit in Loss",IF(G2 < =D2,"SL Taken","")))))))

and if A2 is "Sell" then it uses below formula

=IF(ISBLANK(G2),"",IF(G2 < F2,"Rocked",IF(AND(G2 > =F2,G2 < =E2),"Tgt Done",IF(AND(G2 > E2,G2 < C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2 > C2,G2 < D2),"Exit in Loss",IF(G2 > =D2,"SL Taken","")))))))

Let me explain in Details.

I want formula on H2 cell.

Say, A coloum has value of either "BUY" or "SELL"

3 criterias are there

1. If G2 is blank then H2 must remain Blank.

2. If a1 is "BUY" :
then if G2 > F2, Result would be "Rocked"
IF G2 < =F2 and G2 > =E2, Result would be "Tgt Done"
IF G2 < E2 and G2 > C2, Result would be "Small Profit"
IF G2=C2,Result would be "Exit at Cost"
IF G2 < C2 and G2 > D2), Result would be "Exit in Loss"
IF G2 < =D2,Result would be "SL Taken",

3. Same but reverse formula applies, if a1 is "SELL"
then if G2 < F2, Result would be "Rocked"
IF G2 > =F2 and G2 < =E2, Result would be "Tgt Done"
IF G2 > E2 and G2 < C2, Result would be "Small Profit"
IF G2=C2,Result would be "Exit at Cost"
IF G2 > C2 and G2 < D2), Result would be "Exit in Loss"
IF G2 > =D2,Result would be "SL Taken",

I want all the three conditions in Cell H2 (as shown in picture)

For your information, this is a sheet to track stock performance.
In simple words,If I buy some stock &

If booked price is more than entry price and less than tgt1, than its Small Profit
If booked price is equal to or more than TGT1, than its Tgt Done
If booked price is between TGT1 & TGT2 or equal to TGT2, then its TGT done.
If booked price is more than TGT2, then Rocked.
If booked price is equal to entry price then Exit at cost
If booked price is Less than Entry Price but More than Stoploss then Exit in Loss
If booked price is Less than or equal to Stoploss than SL Taken


In the same way, if i sell some stock &
If booked price is less than entry price and more than tgt1, than its Small Profit
If booked price is equal to or less than TGT1, than its Tgt Done
If booked price is between TGT1 & TGT2 or equal to TGT2, then its TGT done.
If booked price is less than TGT2, then Rocked.
If booked price is equal to entry price then Exit at cost
If booked price is more than Entry Price but less than Stoploss then Exit in Loss
If booked price is more than or equal to Stoploss than SL Taken

 

[dine]

Here you go.

=IF(AND(ISBLANK(G2)=FALSE,A2="Buy"),IF(G2>F2,"Rocked",IF(AND(G2<=F2,G2>=E2),"Tgt Done",IF(AND(G2<E2,G2>C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2<C2,G2>D2),"Exit in Loss",IF(G2<D2,"SL Taken","")))))),IF(ISBLANK(G2),"",IF(G2<F2,"Rocked",IF(AND(G2>F2,G2<E2),"Tgt Done",IF(AND(G2>E2,G2<C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2>C2,G2<D2),"Exit in Loss",IF(G2>D2,"SL Taken",""))))))))

Hope this should work.

 

[dine]

The formula is not pasted completely. check the below one.

=IF(AND(ISBLANK(G2)=FALSE,A2="Buy"),IF(G2 > F2,"Rocked",IF(AND(G2<=F2,G2>=E2),"Tgt Done",IF(AND(G2 < E2,G2 > C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2 < C2,G2 > D2),"Exit in Loss",IF(G2 < D2,"SL Taken","")))))),IF(ISBLANK(G2),"",IF(G2 < F2,"Rocked",IF(AND(G2 > F2,G2 < E2),"Tgt Done",IF(AND(G2 > E2,G2 < C2),"Small Profit",IF(G2=C2,"Exit at Cost",IF(AND(G2 > C2,G2 < D2),"Exit in Loss",IF(G2 > D2,"SL Taken",""))))))))