Add multiple "if" formulas or make a short formula

V

vishu

Board Regular
Joined
Oct 26, 2011
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
I have A to J column that provides buy sell with targets with stop loss
In the cell, "J3 " shows whether the price is triggered or not now want. To add more formulas in the same cell which shows a target hit 1 2 3 and if price moves above 3 than out of range should display

Buy Sell Formula.xlsx
ABCDEFGHIJ
1
2Magic Number9.32EntryIni.Tgt.1st Tgt.2nd Tgt.3rd TgtStrict SLLast Traded Price
3578.00Buy>582.66584.99588.72591.98594.31578.09589BUY
4Sell<573.34571.01567.28564.02561.69
Sheet1
Cell Formulas
RangeFormula
B2B2=C3-C4
D3D3=C3+(B2*0.25)
E3E3=C3+(B2*0.65)
F3F3=C3+B2
G3G3=C3+(B2*1.25)
H3H3=AVERAGE(C3:C4)+B2/100
D4D4=C4-(B2*0.25)
E4E4=C4-(B2*0.65)
F4F4=C4-B2
G4G4=C4-(B2*1.25)
J3J3=IF(A3<I3,IF(I3>C3,"BUY","B Near SL"),IF(I3<C4,"SELL","S Near SL"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J3Cell Valuecontains "SELL"textYES
J3Cell Valuecontains "BUY"textYES
A1Cell Value="LONG"textYES
A1Cell Value="SHORT"textYES
C1Cell Value="LONG"textYES
C1Cell Value="SHORT"textYES
 

Attachments

  • screenshot.jpeg
    screenshot.jpeg
    35 KB · Views: 4

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
vishu said:
I have A to J column that provides buy sell with targets with stop loss
In the cell, "J3 " shows whether the price is triggered or not now want. To add more formulas in the same cell which shows a target hit 1 2 3 and if price moves above 3 than out of range should display

Buy Sell Formula.xlsx
ABCDEFGHIJ
1
2Magic Number9.32EntryIni.Tgt.1st Tgt.2nd Tgt.3rd TgtStrict SLLast Traded Price
3578.00Buy>582.66584.99588.72591.98594.31578.09589BUY
4Sell<573.34571.01567.28564.02561.69
Sheet1
Cell Formulas
RangeFormula
B2B2=C3-C4
D3D3=C3+(B2*0.25)
E3E3=C3+(B2*0.65)
F3F3=C3+B2
G3G3=C3+(B2*1.25)
H3H3=AVERAGE(C3:C4)+B2/100
D4D4=C4-(B2*0.25)
E4E4=C4-(B2*0.65)
F4F4=C4-B2
G4G4=C4-(B2*1.25)
J3J3=IF(A3<I3,IF(I3>C3,"BUY","B Near SL"),IF(I3<C4,"SELL","S Near SL"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J3Cell Valuecontains "SELL"textYES
J3Cell Valuecontains "BUY"textYES
A1Cell Value="LONG"textYES
A1Cell Value="SHORT"textYES
C1Cell Value="LONG"textYES
C1Cell Value="SHORT"textYES
Click to expand...
I tried to work on probably what you need - See if it works for you Check Cell J5
Once a formula is populated - Then start thinking about making it smaller
First Check if that is working for you...

All Records.xlsb
ABCDEFGHIJ
1
2Magic Number9.3199952EntryIni.Tgt.1st Tgt.2nd Tgt.3rd TgtStrict SLLast Traded Price
3578Buy>582.66584.99588.72591.98594.31578.09589BUY
4Sell<573.34571.01567.28564.02561.69
5New ->B T2
Sheet1
Cell Formulas
RangeFormula
B2B2=C3-C4
D3D3=C3+(B2*0.25)
E3E3=C3+(B2*0.65)
F3F3=C3+B2
G3G3=C3+(B2*1.25)
H3H3=AVERAGE(C3:C4)+B2/100
D4D4=C4-(B2*0.25)
E4E4=C4-(B2*0.65)
F4F4=C4-B2
G4G4=C4-(B2*1.25)
J3J3=IF(A3<I3,IF(I3>C3,"BUY","B Near SL"),IF(I3<C4,"SELL","S Near SL"))
J5J5=IFS( AND(A3<I3,I3>C3,I3<G3,I3>F3),"B T1", AND(A3<I3,I3>C3,I3<F3,I3>E3),"B T2", AND(A3<I3,I3>C3,I3<E3,I3>A3),"B T3", AND(A3<I3,I3>C3),"BUY",A3<I3,"B Near SL", AND(I3<C4,I3<E4,I3>F4),"S T1", AND(I3<C4,I3<F4,I3>G4),"S T2", AND(I3<C4,I3<G4,I3>H4),"S T3", I3<C4,"SELL",TRUE,"S Near SL")
 
Upvote 0
Thank you Sanjay but am afraid IFS function not work for me. Showing error #NAME?
As am using excel2016 and this version IFS Function not available.

If any other way than please
 
Upvote 0
vishu said:
Thank you Sanjay but am afraid IFS function not work for me. Showing error #NAME?
As am using excel2016 and this version IFS Function not available.

If any other way than please
Click to expand...
IF and IFS are twins. You can nest upto 16 IF statements if I'm not wrong.

Just convert -
Excel Formula: 
=IFS( AND(A3<I3,I3>C3,I3<G3,I3>F3),"B T1", AND(A3<I3,I3>C3,I3<F3,I3>E3),"B T2", AND(A3<I3,I3>C3,I3<E3,I3>A3),"B T3", AND(A3<I3,I3>C3),"BUY",A3<I3,"B Near SL", AND(I3<C4,I3<E4,I3>F4),"S T1", AND(I3<C4,I3<F4,I3>G4),"S T2", AND(I3<C4,I3<G4,I3>H4),"S T3", I3<C4,"SELL",TRUE,"S Near SL")
to
Excel Formula: 
=IF(AND(A3<I3,I3>C3,I3<G3,I3>F3),"B T1",
IF(AND(A3<I3,I3>C3,I3<F3,I3>E3),"B T2",
IF(AND(A3<I3,I3>C3,I3<E3,I3>A3),"B T3",
IF(AND(A3<I3,I3>C3),"BUY",IF(A3<I3,"B Near SL",
IF(AND(I3<C4,I3<E4,I3>F4),"S T1",
IF(AND(I3<C4,I3<F4,I3>G4),"S T2",
IF(AND(I3<C4,I3<G4,I3>H4),"S T3",
IF(I3<C4,"SELL","S Near SL")))))))))
 
Upvote 0
SanjayGulatiMusafir said:
IF and IFS are twins. You can nest upto 16 IF statements if I'm not wrong.

Just convert -
Excel Formula: 
=IFS( AND(A3<I3,I3>C3,I3<G3,I3>F3),"B T1", AND(A3<I3,I3>C3,I3<F3,I3>E3),"B T2", AND(A3<I3,I3>C3,I3<E3,I3>A3),"B T3", AND(A3<I3,I3>C3),"BUY",A3<I3,"B Near SL", AND(I3<C4,I3<E4,I3>F4),"S T1", AND(I3<C4,I3<F4,I3>G4),"S T2", AND(I3<C4,I3<G4,I3>H4),"S T3", I3<C4,"SELL",TRUE,"S Near SL")
to
Excel Formula: 
=IF(AND(A3<I3,I3>C3,I3<G3,I3>F3),"B T1",
IF(AND(A3<I3,I3>C3,I3<F3,I3>E3),"B T2",
IF(AND(A3<I3,I3>C3,I3<E3,I3>A3),"B T3",
IF(AND(A3<I3,I3>C3),"BUY",IF(A3<I3,"B Near SL",
IF(AND(I3<C4,I3<E4,I3>F4),"S T1",
IF(AND(I3<C4,I3<F4,I3>G4),"S T2",
IF(AND(I3<C4,I3<G4,I3>H4),"S T3",
IF(I3<C4,"SELL","S Near SL")))))))))
Click to expand...
Thank you very much
 
Upvote 0
Hello Sanjay Ji,
am just trying to add one more condition. but giving me an error. please if you could help me will be great.

FALSE
9.32EntryIni.Tgt.1st Tgt.2nd Tgt.3rd TgtStrict SLLast Traded Price
Magic NoBuy>582.66584.99588.72591.98594.31578.09574
578.00Sell<573.34571.01567.28564.02561.69
 
Upvote 0
Hello Sanjay Ji,
am just trying to add one more condition. but giving me an error. please if you could help me will be great.

Buy Sell Formula.xlsx
NOPQRSTUV
4FALSE
59.32EntryIni.Tgt.1st Tgt.2nd Tgt.3rd TgtStrict SLLast Traded Price
6Magic NoBuy>582.66584.99588.72591.98594.31578.09574
7578.00Sell<573.34571.01567.28564.02561.69
Sheet1
Cell Formulas
RangeFormula
V4V4=IF(AND(N7<V6,V6>P6,V6>T6,V6>S6),"Buy Tgt 3 done", IF(AND(N7<V6,V6>P6,V6>S6,V6>R6),"Buy Tgt 2 done", IF(AND(N7<V6,V6>P6,V6>R6,V6>Q6),"Buy Tgt 1 done", IF(AND(N7<V6,V6>P6,V6>Q6,V6>N7),"Buy Ini Tgt done", IF(AND(N7<V6,V6>P6),"BUY",IF(AND(U6<V6,U6>N7),"BUY WAIT", IF(AND(N7<V6,V6>P6),"BUY",IF(AND(U6<P7),"BUY SL HIT", IF(AND(N7>V6,V6<P7,V6<T7,V6<S7),"Sell Tgt 3 done", IF(AND(N7>V6,V6<P7,V6<S7,V6<R7),"Sell Tgt 2 done", IF(AND(N7>V6,V6<P7,V6<R7,V6<Q7),"Sell Tgt 1", IF(AND(N7>V6,V6<P7,V6<Q7,V6<N7),"Sell Ini Tgt done", IF(AND(N7>V6,V6<P7),"SELL",IF(AND(U6>V6,U6<N7),"SELL WAIT", IF(AND(N7>V6,V6<P7),"SELL",IF(U6<P7,"SELL SL HIT"))))))))))))))))
O5O5=P6-P7
Q6Q6=P6+(O5*0.25)
R6R6=P6+(O5*0.65)
S6S6=P6+O5
T6T6=P6+(O5*1.25)
U6U6=AVERAGE(P6:P7)+O5/100
Q7Q7=P7-(O5*0.25)
R7R7=P7-(O5*0.65)
S7S7=P7-O5
T7T7=P7-(O5*1.25)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W7,V4Cell Valuecontains "SL HIT"textNO
W7,V4Cell Valuecontains "Sell Ini Tgt done"textNO
W7,V4Cell Valuecontains "Sell Tgt 1 done"textNO
W7,V4Cell Valuecontains "Sell Tgt 2 done"textNO
W7,V4Cell Valuecontains "Sell Tgt 3 done"textNO
W7,V4Cell Valuecontains "Buy Ini Tgt done"textNO
W7,V4Cell Valuecontains "Buy Tgt 1 done"textNO
W7,V4Cell Valuecontains "Buy Tgt 2 done"textNO
V4Cell Valuecontains "Buy Tgt 3 done"textNO
Q7:T7Other TypeColor scaleNO
R7:S7Expression=#REF!=5textNO
R7:S7Expression=#REF!=6textNO
R7:S7Expression=#REF!=7textNO
R7:S7Expression=#REF!=8textNO
R7:S7Expression=#REF!=9textNO
R7:S7Expression=#REF!=10textNO
R7:S7Expression=#REF!=11textNO
R7:S7Expression=#REF!=12textNO
R7:S7Expression=#REF!=13textNO
R7:S7Expression=#REF!=14textNO
R7:S7Expression=#REF!=15textNO
R7:S7Expression=#REF!=16textNO
R7:S7Expression=#REF!=17textNO
R7:S7Expression=#REF!=18textNO
R7:S7Expression=#REF!=19textNO
Q6:T6Other TypeColor scaleNO
R6:S6Expression=#REF!=5textNO
R6:S6Expression=#REF!=6textNO
R6:S6Expression=#REF!=7textNO
R6:S6Expression=#REF!=8textNO
R6:S6Expression=#REF!=9textNO
R6:S6Expression=#REF!=10textNO
R6:S6Expression=#REF!=11textNO
R6:S6Expression=#REF!=12textNO
R6:S6Expression=#REF!=13textNO
R6:S6Expression=#REF!=14textNO
R6:S6Expression=#REF!=15textNO
R6:S6Expression=#REF!=16textNO
R6:S6Expression=#REF!=17textNO
R6:S6Expression=#REF!=18textNO
R6:S6Expression=#REF!=19textNO
V4Cell Valuecontains "SELL"textYES
V4Cell Valuecontains "BUY"textYES
N4,S4:T4,P4:Q4Cell Value="LONG"textYES
N4,S4:T4,P4:Q4Cell Value="SHORT"textYES
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: IF AND formula error "FALSE"
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
vishu said:
Hello Sanjay Ji,
am just trying to add one more condition. but giving me an error. please if you could help me will be great.

Buy Sell Formula.xlsx
NOPQRSTUV
4FALSE
59.32EntryIni.Tgt.1st Tgt.2nd Tgt.3rd TgtStrict SLLast Traded Price
6Magic NoBuy>582.66584.99588.72591.98594.31578.09574
7578.00Sell<573.34571.01567.28564.02561.69
Sheet1
Cell Formulas
RangeFormula
V4V4=IF(AND(N7<V6,V6>P6,V6>T6,V6>S6),"Buy Tgt 3 done", IF(AND(N7<V6,V6>P6,V6>S6,V6>R6),"Buy Tgt 2 done", IF(AND(N7<V6,V6>P6,V6>R6,V6>Q6),"Buy Tgt 1 done", IF(AND(N7<V6,V6>P6,V6>Q6,V6>N7),"Buy Ini Tgt done", IF(AND(N7<V6,V6>P6),"BUY",IF(AND(U6<V6,U6>N7),"BUY WAIT", IF(AND(N7<V6,V6>P6),"BUY",IF(AND(U6<P7),"BUY SL HIT", IF(AND(N7>V6,V6<P7,V6<T7,V6<S7),"Sell Tgt 3 done", IF(AND(N7>V6,V6<P7,V6<S7,V6<R7),"Sell Tgt 2 done", IF(AND(N7>V6,V6<P7,V6<R7,V6<Q7),"Sell Tgt 1", IF(AND(N7>V6,V6<P7,V6<Q7,V6<N7),"Sell Ini Tgt done", IF(AND(N7>V6,V6<P7),"SELL",IF(AND(U6>V6,U6<N7),"SELL WAIT", IF(AND(N7>V6,V6<P7),"SELL",IF(U6<P7,"SELL SL HIT"))))))))))))))))
O5O5=P6-P7
Q6Q6=P6+(O5*0.25)
R6R6=P6+(O5*0.65)
S6S6=P6+O5
T6T6=P6+(O5*1.25)
U6U6=AVERAGE(P6:P7)+O5/100
Q7Q7=P7-(O5*0.25)
R7R7=P7-(O5*0.65)
S7S7=P7-O5
T7T7=P7-(O5*1.25)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W7,V4Cell Valuecontains "SL HIT"textNO
W7,V4Cell Valuecontains "Sell Ini Tgt done"textNO
W7,V4Cell Valuecontains "Sell Tgt 1 done"textNO
W7,V4Cell Valuecontains "Sell Tgt 2 done"textNO
W7,V4Cell Valuecontains "Sell Tgt 3 done"textNO
W7,V4Cell Valuecontains "Buy Ini Tgt done"textNO
W7,V4Cell Valuecontains "Buy Tgt 1 done"textNO
W7,V4Cell Valuecontains "Buy Tgt 2 done"textNO
V4Cell Valuecontains "Buy Tgt 3 done"textNO
Q7:T7Other TypeColor scaleNO
R7:S7Expression=#REF!=5textNO
R7:S7Expression=#REF!=6textNO
R7:S7Expression=#REF!=7textNO
R7:S7Expression=#REF!=8textNO
R7:S7Expression=#REF!=9textNO
R7:S7Expression=#REF!=10textNO
R7:S7Expression=#REF!=11textNO
R7:S7Expression=#REF!=12textNO
R7:S7Expression=#REF!=13textNO
R7:S7Expression=#REF!=14textNO
R7:S7Expression=#REF!=15textNO
R7:S7Expression=#REF!=16textNO
R7:S7Expression=#REF!=17textNO
R7:S7Expression=#REF!=18textNO
R7:S7Expression=#REF!=19textNO
Q6:T6Other TypeColor scaleNO
R6:S6Expression=#REF!=5textNO
R6:S6Expression=#REF!=6textNO
R6:S6Expression=#REF!=7textNO
R6:S6Expression=#REF!=8textNO
R6:S6Expression=#REF!=9textNO
R6:S6Expression=#REF!=10textNO
R6:S6Expression=#REF!=11textNO
R6:S6Expression=#REF!=12textNO
R6:S6Expression=#REF!=13textNO
R6:S6Expression=#REF!=14textNO
R6:S6Expression=#REF!=15textNO
R6:S6Expression=#REF!=16textNO
R6:S6Expression=#REF!=17textNO
R6:S6Expression=#REF!=18textNO
R6:S6Expression=#REF!=19textNO
V4Cell Valuecontains "SELL"textYES
V4Cell Valuecontains "BUY"textYES
N4,S4:T4,P4:Q4Cell Value="LONG"textYES
N4,S4:T4,P4:Q4Cell Value="SHORT"textYES
Click to expand...
We need to abide by the rules of the Forum - I shall reply once Fluff tells me to do so...
 
Upvote 0
You do not need to wait for my approval. We do not lock threads here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
What if investment
Replies
2
Views
65
tigerzen
T
K
  • Question Question
Populate With Criteria
Replies
3
Views
79
Peter_SSs
Peter_SSs
K
  • Solved
Formula to match two columns and get a result from third one
Replies
5
Views
342
Fluff
Fluff
E
  • Question Question
Copying/pasting the selected data from a query table to excel sheet
Replies
1
Views
407
erfo
E
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top