• 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.

Solution to solve the Movement criteria to know whether its Gap up or Gap Down or No Movement?

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear Excel Experts,

In the attached excel sheet you will find a column named 'Diff". I need to give a formula in the "movement" column based on the values in the "Diff" column, The rules are as follows:-

1. If the value >= 30 "Gap Up"
2. If the value <= 30 "Gap Down"
3. if the diff is less than 15 it should show "No Movement"

I tried to do with if condition based on the first two criteria and the results were coming correct. If i give the 3rd criteria all the results coming wrong.

Hope some experts will look into it and solve the issue.

Regards,

Sonjoe
 

Attachments

  • Working Sheet.xlsx
    10.3 KB · Views: 10
Try,

1] Set up a Lookup Table in H3:H5 as per follow picture

2] In E3, formula copied down :

=IF(E3="","",LOOKUP(E3,H$3:J$5))

Edit: Check the Lookup Table, ensure the range in meet with your requirement.

81189
 
bosco_yip said:
Try,

1] Set up a Lookup Table in H3:H5 as per follow picture

2] In E3, formula copied down :

=IF(E3="","",LOOKUP(E3,H$3:J$5))

Edit: Check the Lookup Table, ensure the range in meet with your requirement.

View attachment 81189
Click to expand...

Bosco i checked yours is almost 95% correct but i got three errrors which are marked in red. Kindly go through do edit how to rectify that ok. The file is attached long with this message.
 

Attachments

  • Working Sheet.xlsx
    11.1 KB · Views: 3
If these are trading prices of a security/financial instrument then I thought that what's called an opening gap is when the open is outside the previous day's entire price range, no? (Usually, a gap is when an entire day's trading is completely outside the previous day's entire trading range)
So sticking to only look at the open, and with a price around 20k you're calling a gap a gap if the open is more than 30 above/below the previous day's entire trading range, then something like this in a cell in row 4:
Code: 
=IF((C4-MAX(C3:D3))>=30,"Gap up",IF((C4-MIN(C3:D3))<=-30,"Gap down","No movement"))
copied down.
I realise this is all wrong because we don't have the lows and highs of each day.
 
Last edited:
SONJOE JOSEPH
Based #4 reply ...
If the value is 30 ... then ... would it be "Gap"?​
About Your It would be gap up friend
Based #1:
1. If the value >= 30 "Gap Up"
2. If the value <= 30 "Gap Down"
Take care
 
p45cal said:
If these are trading prices of a security/financial instrument then I thought that what's called an opening gap is when the open is outside the previous day's entire price range, no? (Usually, a gap is when an entire day's trading is completely outside the previous day's entire trading range)
So sticking to only look at the open, and with a price around 20k you're calling a gap a gap if the open is more than 30 above/below the previous day's entire trading range, then something like this in a cell in row 4:
Code: 
=IF((C4-MAX(C3:D3))>=30,"Gap up",IF((C4-MIN(C3:D3))<=-30,"Gap down","No movement"))
copied down.
I realise this is all wrong because we don't have the lows and highs of each day.
Click to expand...

Do u need the lows and highs ....i will send if u want....i want to make this right. Do message me
 
p45cal said:
If these are trading prices of a security/financial instrument then I thought that what's called an opening gap is when the open is outside the previous day's entire price range, no? (Usually, a gap is when an entire day's trading is completely outside the previous day's entire trading range)
So sticking to only look at the open, and with a price around 20k you're calling a gap a gap if the open is more than 30 above/below the previous day's entire trading range, then something like this in a cell in row 4:
Code: 
=IF((C4-MAX(C3:D3))>=30,"Gap up",IF((C4-MIN(C3:D3))<=-30,"Gap down","No movement"))
copied down.
I realise this is all wrong because we don't have the lows and highs of each day.
Click to expand...

Tried using the formala....but some of them coming wrong. Even i entered the High and Low prices and applied ur formula but still coming wrong.
 
SONJOE JOSEPH said:
Bosco i checked yours is almost 95% correct but i got three errrors which are marked in red. Kindly go through do edit how to rectify that ok. The file is attached long with this message.
Click to expand...
1] Please ensure your Lookup Table range order, from small to large

2] In respect to your expected result, I suggest a Lookup Table as in L3:N6

In G3, formula copied down :

=IF(E3="","",LOOKUP(E3,L$3:N$6))

81201
 

Attachments

  • Lookup(Bosco1).xlsx
    15.1 KB · Views: 3
You must log in or register to reply here.
Back
Top