Respected,m
Thank you for trying to help me.
will post a sample sheet with data by today or tomorrow(20jan or 21jan)
Zohar Batterywala
Respected,m
Thank you for trying to help me.
will post a sample sheet with data by today or tomorrow(20jan or 21jan)
Zohar Batterywala
Respected,
Though value() formula and isnumber() shows it was correct. The record was not recognised.
Is there any formula to find format of a cell(like dd-mmm-yy hh:mm Example: 8-dec-2021 11:20)
Zohar Batterywala
Respected ,
Made the formula more clear.
formula in B76,B79,B82,B85 are main formulas
The formulas in line above that
(means B75.c75,d75.e75 And so on is just done to ease calculation process at this time .it is not needed in final version)02-11-2020-TO-22-01-2021NTPCEQN.xlsx
Zohar Batterywala02-11-2020-TO-22-01-2021NTPCEQN.xlsx
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
Respected Carlm,
My main aim is to find the movement. for the I am able to find the trend of other things(like volume, Average price, etc. ) but here as the combinations are more, has to take care so that each condition gets evaluated AND important is that nothing remains unevaluated.
In condition there are
2 at first stage
4 at second stage
4 at last stage.
,so ~32 combinations do occur but it might happen that
from 1 stage , checking might goes in one side, &
so condition 3,4 of 2nd stag
& 3,4 of 3 stage might not come in checking.
though this might be applicable and make impact on analysis
and so rather than needing to find various candlestick/chart patterns, I have to find just how the last candle performed in relation to previous candle.
The problem with the issue is that (as I have mentioned earlier also) there are about 30+combination and a particular condition might not trigger as the preceding conditions negates that condition,(logical error) . This will now be evident in that calculation but in next calculation by which time that analysis is of no use---so has to understand the logic first as to which way I should make the code so that all the conditions are properly evaluated.
Had posted in mr. excel forum -- https://www.mrexcel.com/board/…ion.1159249/#post-5624414
Respected,
Day Open High Low Close
D1 O1 H1 L1 C1
D2 O2 H2 L2 C2
Wants to get the logic- for excel formula for decoding OpenHighLowClose for candlestick patterns--
The details are --(1) (a)BUllish or (b)bearish
(2)(a)Inside bar (a)Above Bar (b) Belowbar(c)Normal Bar(d)Above normal Bar(body is above >50 of previous days body)(e)Below Normal Bar
(3)(a)Upper wick/shadow is longer than lower wick(b)Lower Wick/shadow is longer than Upperwick(c)Same size but same as body , so no spinning top like thing(d)Upperwick, Lowerwick and body all are of Same Size(~98%)(e)Spinning top pattern.
the structure I need is like--(1) if c(lose>O(open), then bullish else bearish , then go to second stage(2)If c2(today)>c1(yesterday) & o2<o1, inside bar, if c2<c1& o2>o1, its outside bar, if o2<o1& c2>50% of previous days c1-o1, then up Inside bar, ..... like that there are about 30+combination WHILE excel support only 7 levels of IF.--- so importnt to exactly know the logic as what to evaluate first-second-third.......
I am putting emphasis on logic as it has happened many times in other functions---- that a particular condition does not trigger as the preceding conditions negates that condition,(logical error), so I want to first clear the exact thought process----means steps of it in going through various conditions ---before constructing formula for that.
PLEASE HELP
-------I want logic as has to make the formulas to suit at various place.
Zohar Batterywala
sir, the total excel file is of 1.3 GB and I have tried using conditional formatting previously but it has slowed even the normal pageup/down thing tremendously. I am looking for logic of this , then I have to convert it to workable formulas in 14 files(each having 8 instances)
HOPE
Zohar Batterywala
Respected Sir ,
PRACTICAL: The issue is that when the levels in the upper reservoir is too much it is released in the lower reservoir , now normallly it is happening that this release is sometimes more than the capacity the lower reservoir can hold(and the structure is endangered).
The solution I am trying: to monitor level of the reservoir so that difference(range between the opening and closing) is spotted.
when the extra level is released from upper reservoir, a step-by-step movement has to be done so that immense flow does not come to lower reservoir which it can handle. So am trying to monitor (daily levels are updated every 4 hours , so 6 in a day). And similar step-by step can be released further.
And the SECOND case is that if the increaseing levels is spotted in above feeder, lower feeder levels decreased pre-emptively to make way for the incoming future flow.
OB-Overflow build up is spotted only after 3rd reading as it is normal that levels increase or decrease in normal flow. but 3rd consequence of it do trigger the situation. OB#, OB$, OB5, OB6 do mention that and when the OVERFLOW BUILDUP is released to lower reservoirs, it is flagger by OR
ORA is flagging of abnormal levels seen(in lower reservoirs) if only one or two reading is too much fluctuating (due to OR from upper reservoir)
Zohar
Respected,
I am trying to formulate a way so that increasing/decreasing level in a reservoir can be known before hand so controlled flow can be assures.
Zohar Batterywalarange.xlsx
Respected Bosco_YIP,
THANK YOU VERY VERY MUCH
zohar Batterywala
Respected Sir Glenn,
Yes sir it was mistake by me
Originally I had included the formula that was in D in the main formula in E
The issue is solved at this time.
Will request your help if I will need it.
The issue with me is cant find a correct answer if has done a mistake once as that type of thought do continue when reviewing the problem. In that case others perspective make a big difference for me.
THANK YOU SIR
Zohar Batterywala
Respected,
I am SORRY
but I find the previous attachment to be not proper that I saved at last and uploaded so am uploading again
Zohar
Respected Sir,
I had tried to use conditional formatting before but the plain file without any formatting is itself of 1.1 GB and takes 15-20 minutes for opening and 7-8 minutes to save and (conditional) formatting makes it more heavy.
I am using This file just to calculate data (as 90% of formulas needs past data of atleast 15 days).
Then this data is moved to an value only file (which is just becoming 200 MB).
Zohar Batterywala
Respected Sir Roy,
This are the alerts
Range is just for indication -it not to be included in formula
High(A) Low(B) Last(C) Range alert/Mention(In E column)
(1)8404 8390.15 8390.15 16.78 WLN▼ Last is vry low near low.Her▲also comes in range but last is low--needed to alert that level is too much low
Row 3 in file
(2)8410.35 8395.5 8410.35 16.82 WLN▲ Last is very high near high Here ▼ also comes in range but last is high--needed to alert that level is too much high
Row 9 in file
When Last reading is same as high or low Alert WLN▼(LOW) or WLN▲(HIGH) OR in 0.005%(ad not mentioned in file but I find this recently--Sorry for that)
WLN=Warning Last Near
-------------------------------------------------
Normal Case
High Low Last Range Mention(In E column)
(3)8415 8385 8398.35 16.8 LN▼ here more near is low
Row 5 in file
(4)8415.35 8384.5 8400.35 16.8 LN▲ here more near is HIGH
Row 7 in file
(4)8441 8373.15 8400.35 16.8 OR none is in range-OUT OF RANGE
Row 11 in file
Originally in the sheet which I had to keep this formula here DATA is sergegared but will manage adjusting that in formula. This is the functionality scenario.
THANK YOU
Zohar Batterywala
Respected Sir Roy,
Needs HELP as the formula I made was not addressing all cases.
Needs to make formula to alert/mention based on the level
This is the same file that is attached in Initial post.
Zohar
Respected Madam AliGW,
That is not the Post I have posted Here.
I will post that URL and the Query both.
https://www.excelforum.com/exc…-another.html#post5269512excel problem@27jan2020.xlsx
HELLO,
Needs HELP as the formula I made was not addressing all cases.
Needs to make formula to alert/mention based on the level
Zohar
Respected Glenn Sir,
THANK YOU VERY VERY MUCH
Zohar Batterywala
Respected Sir,
Part of it was solved by a friend
=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A3,"%",REPT(" ",255)),255),"NF‰",REPT(" ",255)),255))
for extracting nf%
but in that I cant understand logic and how to use it for extracting checksums and other data
Zohar
Respected Sir,
I think it needs formulas as I have to use it further in other formulas and the automated things of excel like FILL SPACE(or something similar do not update the data when source data is changed.
Zohar