Respected,

Has tried to create formula but had many exceptions which I was not able to coordinate-so asking your help PLEASE PLEASE

-----------------------------------------------------------

Last exception is kept to just check the execution.it will be removed in final

---

=IF(AND(OR(B83<B80,E83<E80),ABS(E83-B83)<ABS(E80-B80)),IF(E83>B83,

IF(B83>=AVERAGE(B83,E83),IF((C83-B83)>=1.5*(E83-D83),"11IBuTW","12IBuLW"), (to see if the lower/upper is prominent)

"13IBuL"), whether the body is in upper/lower half of previous candle

"14IBBr"), whethere the body is bullish(close>open) or bearish(close<open)

"15NIB║")

&"║2ndcase"&

IF(AND(OR(B83>B80,E83>E80),ABS(E83-B83)>ABS(E80-B80)),IF(E83>B83,IF(E83>=AVERAGE(B83+E83),IF((C83-B83)>=1.5*(E83-D83),"21OBuTW","22OBuLW"),"23OBuL"),"24OBBr"),"25NOB")

&"║3rd case"&

IF(OR(AND(E83>E80,OR(B83>E80,B83>B80))),IF(E83>B83,IF(E83>=AVERAGE(B83+E83),IF((C83-B83)>=1.5*(E83-D83),"31BuUTW","32NBuUW"),"33NBuU"),"34NBuUBr"),"35NNBU")

&"║4th case"&

IF(OR(AND(E83>E80,OR(B83>E80,B83>B80))),IF(E83>B83,IF(E83>=AVERAGE(B83+E83),IF((C83-B83)>=1.5*(E83-D83),"41NBuLTW","42NBuLW"),"43NBuL"),"44NBrL"),"45NNBL")

---

Last exception(all 5 in 2nd place)is kept to just check the execution.it will be removed in final

Only one of 20 conditions fulfill and so only one of that should be displayed

d1 o(pen)1 h(igh)1 l(ow)1 c(lose)1

d2 o2 h2 l2 c2

--

case i "inside bar"

if(and(abs(c2-o2)<abs(c1-o1),not(c2>c1),not(o2<o1)

if(c2<o2),"bullish inside bar" ELSE "bearish inside bar"

if((c2-c1)>(o2-o1),(c2-c1)>(o2-o1)"lower insider bullish bar"

if((c2-c1)<(o2-o1),(c2-c1)<(o2-o1)"upper insider bullish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

case 2 "outsider bar"

if(and(abs(c2-o2)>abs(c1-o1),not(c2<c1),not(o2>o1)

if(c2>c1),"bullish outside bar" ELSE "bearish outside bar"

if(and((c2-c1)>(o2-o1)),c2>c1,o2>o1),"upper outside bullish bar"

if((c2-c1)<(o2-o1),c2<c1,o2<o1)"lower insider bullish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

case 3 "upper normal bar"

if(and(c2>c1,o2>o1,not(o2<o1))

if(c2<o2),"upper normal bullish bar",ELSE "upper normal bearish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

case 4 "lower normal bar"

if(and(c2<c1,o2<o1,not(o2>o1))

if(c2<o2),"lower normal bullish bar" ELSE "lower normal bearish bar"

if(h2-c2)>2x(c2-l2)"long upper wick"

if(h2-c2)<2x(c2-l2)"long lower wick"

Zohar Batterywala02-11-2020-TO-22-01-2021NTPCEQN.xlsx