Solved

Math Code Modification

Posted on 2014-03-26
79
154 Views
Last Modified: 2014-04-08
Hi Experts,

In attached WB there is already one Code is there. In which I want to add one criteria & investigate code as if I do calculation manually it shows criteria meet but while running code it by pass that point.

1.Add this criteria in existing code. Before procedure start.
Copy formula from cell G7 till end "DropDwon". Procedure start.
while procedure running ignore rows in calculation  which have "Ignore" in column G.

2.Investigation
Code do this procedure
Copy formula from Cell H2 & I2 till end. if in column I found "Hit" then find lower or higher values from last point date which in range L3:N3 to current hit row date.
Shift down whole data in range L3:N3 one row down & register new data.
Clear all formulas in Column H & I from row 3 till end.
Copy formula from H2 & I2 & past to where last hit found till end.

Query Part
If I put manually formula to row 132 to till end (in attached yellow highlighted) I found "Hit" in row 172 & previous point is High so need to find lowest point from range =MIN(D132:D172) which is 1012.10 in row 162. but code by pass this point.   I don't know why?

reply me back if any part is confusing  or for question on this issue.

Thanks
Math-G-V04.xlsm
0
Comment
Question by:itjockey
  • 49
  • 30
79 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39955687
Couple questions

Re 1)
What code ie what procedure ?
Why G7 till end ? your data starts at G3 shouldn't be G3 tlill end ?

Re 2)
Is this new procedure or existing procedure ? If Existing what is its name ?

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39955786
Couple questions
Re 1)
•What code ie what procedure ?
•Why G7 till end ? your data starts at G3 shouldn't be G3 tlill end ?

Add mention procedure at very first stage of macro start.
Bcoz G7 - as if I copy formula to G3 & dropdown it procedure #ref# error.

Re 2)
Is this new procedure or existing procedure ? If Existing what is its name ?

Yes existing procedure but I don't know which sub calculate that part. as this code is created by Mr.Rob (tagit) & Sir. gowflow and I am not very good in coding.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39955911
I am on my way back to home I ll not available for an hour. (Only Comments Via Mobile)


Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39956744
Any more questions?   Sir?
0
 
LVL 8

Author Comment

by:itjockey
ID: 39958115
As I see & logical thinking I guess this part perform copy formula from Cell H2 & I2 & past from last hit found row to till end.

Sub WriteFormulas(ByVal curRow As Integer, ByVal lastRow As Integer)
    Range("H3:I10000").Clear
    
    Range("H2").Select
    Selection.Copy
    
    Range("H" & curRow & ":H" & lastRow).Select
    ActiveSheet.Paste

    Range("I2").Select
    Selection.Copy
    Range("I" & curRow & ":I" & lastRow).Select
    ActiveSheet.Paste

End Sub

Open in new window


Thank You
0
 
LVL 8

Author Comment

by:itjockey
ID: 39958660
???
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39959002
Your macro is too confusing. If you want me to look at it you need to tell me in plain English (I mean in words) what do you expect to happen when you hit the button that triggers the macro Calculate. As presume all happens when you hit the button called: Find.

I see that you have 2 sets of data
First set from Col A to F
Second set from L to W

So basically what is the data that is 'data' like fix data and what the macro is supposed to do.

If you can explain this then I can follow the logic and see where it flops.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959621
Sorry for delay in reply - two things

A.Do u want to write complete new Code ?
B.or modify the existing one?

if A then have to clarify whole process from start.
if B then little wording which is bit less then A.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39959666
for sure B !!!!

the code is fine I think it is a small issue but sometimes small isssues takes ages to be found especially when one does not know the logic !

I need to know the logic.

try to explain as much as you can don't go in details I can figure out things but need an overview. If I need more details I will ask.

What I found is that it first start by taking the first date that is in L3 then look in Col A to find that date and when it finds it it put the formula in H and I that is a copy of H2 and I2 from that row found till end of data in col A

So what are these 2 formula in H and I are they correct like they are as they refer to a row that is above and row that is under like it refer to row1 and row3 !

then also you said you start by G7 coz you have a REF for sure as the formula points to last 5 rows above
=IF((C7-D7)>(AVERAGE((C3-D3),(C4-D4),(C5-D5),(C6-D6)))*7,"Ignore","")
here we are in row 7 and we are taking the average of row 6,5,4,3 and multiply this by 7
meaning you take the average of the last 4 days multiply by 7 and compare it to the actual if it is smaller then you put "Ignore" in that cell.

if this formula is correct then I can simply modify it to adapt from first row of data.

pls tell me in short AGAIN what is the problem, what is the routine missing ?
gowflow
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959784
if you do it manually press Reset button.

There is Data which need to process - Column A to F.
There is Data in Range L3:M3 & O3:W3 is formula which replicate data for the date in N3 from Column A To F.

01.

Step 1 Find the row number of date which in Cell N3 in column A.(in Attached file it is Row 3)

02.

Step 2 Copy formula From H2 & I2 & past to H3 & I3 as row number is 3 in Step 1.

03.

Step 3 Formula Drop Down Till end.

04.

Step 4 Find Word "Hit" in Column I (in attached it is found at row 32)

05.

Step 5 If L3="High" then Find lowest(Minimum) Value from column D & range is D3:D32 (Step 1 Row - Step 4 Row) in attached it row 30
       If L3="Low"  then Find Highest(Max) value From Column C & Range is C3:C32 (Step 1 Row - Step 4 Row)

06.

Step 6 Shift one row down range L3:W3 but formula remain in row 3.

07.

Step 7 register new point which is found in Step 5 with it date (i attached it comes point 974 date 2/10/2011 & we found low point so L3="Low"
       So now L3="Low" M3=974 & N3=2/10/2011 & O3:W3 have formula which replicate data for for date.

08.

Step 8 Step 1 & So on
This is the whole process. Now problem area if you do it manually put L3="High" M3=1245.63 & N3= 7/8/2011 so date 7/8/2011 is found in Row 132 so copy formula from H2 & I2 & past to H132 to I132 & drop Down till end. you will find "Hit" in I172 as L3=High so finding lowest values from range D132:D172 @ Row 162 (D162) & date is 8/22/2011. which is missing if we run the code.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959787
sorry i seen your post after posting mine.......
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959802
Yes this formula is correct =IF((C7-D7)>(AVERAGE((C3-D3),(C4-D4),(C5-D5),(C6-D6)))*7,"Ignore","")
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959844
in Short

Now problem area if you do it manually put L3="High" M3=1245.63 & N3= 7/8/2011 so date 7/8/2011 is found in Row 132 so copy formula from H2 & I2 & past to H132 to I132 & drop Down till end. you will find "Hit" in I172 as L3=High so finding lowest values from range D132:D172 which we found @ Row 162 (D162) & date is 8/22/2011. which is missing if we run the code.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39959898
No it is working here. What version of Excel you have?
gowlfow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959939
2007 @ Home
2010 @ Office

Both have same problem
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39959967
I just put manually in L3 like you said L3="High" M3=1245.63 & N3= 7/8/2011 and I did a trace on the macro and it gave me this now in L3
Low      1012.099976      8/22/2011

is it correct ? I have not changed anything to the code.

do you hv the problem in 2007 and 2010 ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959976
both
@ my place 2007 (i am @ home)
@ office 2010
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959984
See attached i had highlighted problem area in column L To N.
Math-G-V04.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39959994
Your Name is Jack? Sir?
0
 
LVL 8

Author Comment

by:itjockey
ID: 39960119
May take your leave Sir ...late night here 1:45 AM need to wake in the morning 6:30 AM..?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39960445
The code is correct.

I did the following I put a stop at date 7/8/2011 so I can track what happens till the supposingly date of 8/22/2011 that you claim did not catch.

So as you can see in the attached file, the date of 7/8/2011 falls in row 132 and the first Hit is on row 267 and NOT on row 172 like you claimed it or put it manually.

So if you look at row 267 I put the formula in the red cell and it give you the value of 646.1 which correspond to the date of 12/21/2011 on row 242 which is the correct data.

Don't know how you came up with the Hit on row I172 ??

chk the file
gowflow
Math-G-V05.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39960938
ok Sir here it is - I had deleted all data above 7/8/2011 & put L3=High, M3=1245.63 & N3=7/8/2011. After that run the code. surprisingly we got missing point on date 8/22/2011.


See attached

Thanks
Math-G-V05.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39960972
Don't understand what is wrong but nothing seem to be wrong !!!
I ran the macro as is and here are the results.
gowflow
Math-G-V05A.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39960993
you are asking me or you telling me? as we run code for whole data date 8/22/2011 is missing but if we reduce data & run same code we found 8/22/2011.

any way out or overcome this?

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961003
let me check this again !!! :(

good point !
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961005
Well the only thing I see here is the formula for Gap and Hit

and I go back to the same question I asked before where you said bluntly they are correct.

If you tell me how these are supposed to be calculated then I will review these formulas. As you noticed when the data was complete the hit was on row 267 where you had a hit on row 172 so for same block of data we have different hit !!!

Please explain both formula how they need to be calculated as the logic for the macro is 'I think fine' unless some hidden things I have not discovered yet.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961094
Explanation For Formulas.


Formula For Gap Logic

it finds range between each data point from previous point (previous point  may High or Low).
if current data point range is grater then previous (one row up result) then formula  returns to previous point +/- current high or low point. if it is lesser then previous result (one row up result) it returns to previous result.

Formula For Hit Logic

Here there is 4 Criteria to meet. (Assuming we are calculating for High)

01.

H2>=H4 Current Gap (H Column Value) is Greater then or equal to forward 2 value (H Column Value)

02.

$M$3-(H2*61.8%)<C2This part find if current high or low is above or below to the 38.20% of total range what we find in column H38.20% CrossOver

03.

((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10This part finds current row number & previous point date row number if that is greater then 10 then true

04.

(ABS($M$4-D2)/D2)>3%)This part finds previous point & current point is 3% +/- , if yes then True else false.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961106
I strongly believe part 2 is may be confusing for you. if any thing more on that part pls revert back me as it is purely technical analysis related. I had taken 5 hours to implement that part at very beginning. but once I understand it is too easy for me.

I dint explain more on that part  as if I write It may create too much confusion for you.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961411
ok fine, by part 2 you mean Hit formula Col I ??

I know and see you are also a quite knowledgeable expert in excel here so you knowing your stuff and explaining in detail the formulas do you believe the formulas are correct ? If you say so then I will be looking elsewhere.

I also need to understand the ATR formula Col G why does it start at row 7 what should it calculate and how ? something here does not make sense. Do you need this column for the hit ?

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961499
ok fine, by part 2 you mean Hit formula Col I ??
See heading in previous  comment, there are two heading. Formula for GAP which is for solely Column H & Formula For Hit heading which for column I. Formula For Hit heading have 4 part. basically I had split the criteria.

So yes formula for column Hit but second condition of AND Statement.

I know and see you are also a quite knowledgeable expert in excel here so you knowing your stuff and explaining in detail the formulas do you believe the formulas are correct ? If you say so then I will be looking elsewhere.
I am learning here so may be I am wrong on my part. but first look I dint find any thing wrong in my formula.

I also need to understand the ATR formula Col G why does it start at row 7 what should it calculate and how ? something here does not make sense. Do you need this column for the hit ?

Basically ATR = Average True Range in technical term. & actual calculation is bit different. Which is not required for this purpose. I just added for header Sake.        
why it is start from row 7 ?
just copy from G7 & past to G2..... Error message "#VALUE!" & formula look like this "=IF((C2-D2)>(AVERAGE((#REF!-#REF!),(#REF!-#REF!),(#REF!-#REF!),(C1-D1)))*7,"Ignore","")" so by code like column H & I copy & past it wont work.

it is calculate range of each day ( High - Low) what I did is average of past 4 days & compare with present day. if present day is higher then multiple of 7 then I want to remove that day from calculation.
why m I doing this ? as you know some time in electronic trading market prices sharply bottom down. bcoz of error in punching - stop loss trigger anomalously  - so it effect presentation of graph as well as technical studies so need to ignore that days.

No it is not required directly for Column Hit, but yes indirectly while running code. need to ignore these days it may effect final results of points.
Punching Error which Creates a cascading Effect of Triggering stoploss
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961590
if present day is higher then multiple of 7

the formula Multiply by 7 is that what you want ? or you want to see if it is a multiple of 7 2 different things. Multiple of 7 is a number divided by 7 gives 0 as a reminder like 7, 14,21,28,32 etc...

so which one is it ? All 3 formulas you created ? or helped by an other expert in EE ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961601
Multiply by 7 static not multiple of 7 & all three formula created by me only.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961608
ok you I think it is all in the Hit formula.

say this to me more in English
What is the meaning of Hit ?? when do you consider in Market/Trading terms that you have a Hit !!

You have a Hit when .....

Please continue the sentence.
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961679
Question :

Why
01 H2>=H4 Current Gap (H Column Value) is Greater then or equal to forward 2 value (H Column Value)

Why 2 values not next value ???
like H2>=H3 ....
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961688
I don't know but maybe I found it !!! :))))

try this file.
gowflow
Math-G-V06.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961751
i am little upset i had written whole comment & about to post but some how browser refresh & all i lost.  

Wow !!!!

i can see 7/12/2011 date in result.....what is the problem?

thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961757
ignore condition is added in recent file? i.e. in code?

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961765
what ? sorry don't understand your comment. Did you test the file is all ok ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961769
opps sorry 8/22/2011 found in result..
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
ID: 39961772
ok so is everything correct ? let me know then I will tell you my comments.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961818
as per my very first comment on question part investigation is done...just need to confirm part add criteria  "Ignore" is added or not? as i go through result it seem it is included in code.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961830
No where in the code that you provided nor what I corrected is any check done for Ignore.
I have the regret to tell oyu that who ever wrote this code never took into consideration the Ignore !!!

In plain English it is totally IGNORED !

Please confirm that beside the Ignore issue all is ok with the data ?
gowlfow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961849
yes
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961885
At that point of time i dint ask for ignore criteria. it now i want to amend. that time i just want to draw basic picture of my whole trading project. now all basic picture is drawn in my mind. now step by step final touch - adding data - back test - future prediction with time & price.

i don't claim my strategy is unique but in my known circle no one use this kind of strategy. Currently i take my trade decision based on charts which takes hours & human emotional innervation but after this whole project i just need to click 2 or 3 buttons & it will find most probable profit trade in minute.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961917
You seem at least to be well knowledgeable in your field which is Trading. !!!

I can tell you that if all the data is fine and now you can see the item that was missing before is only due to the Hit Formula as I suspected. !!! :)

this is your original formula:
=IFERROR(IF($L$3="High",IF(AND(H2>=H4,$M$3-(H2*61.8%)<C2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),IF($L$3="Low",IF(AND(H2>=H4,$M$3+(H2*61.8%)>D2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),"")),"")

and this is my correction:
=IFERROR(IF($L$3="High",IF(AND(H2>=H3,$M$3-(H2*61.8%)<C2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),IF($L$3="Low",IF(AND(H2>=H4,$M$3+(H2*61.8%)>D2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),"")),"")

Notice the H2>H4 you were comparing each other row where in fact you need to compare current row H2 with Next Row not the row after reason why you were missing that 8/22/2011 (that you probably noticed) and missed a lot more that you didn't notice !!!

Let me know your comments.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961935
=IFERROR(IF($L$3="High",IF(AND(H2>=H3,$M$3-(H2*61.8%)<C2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),IF($L$3="Low",IF(AND(H2>=H3,$M$3+(H2*61.8%)>D2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),"")),"")


hmmm true.....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961969
hhhmmmm correct !! see I told you, your a pro in your business !!! :)

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961971
ok so for ignore criteria do u want me to ask new question or you will amend in this question?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39961984
hhhmmmm correct !! see I told you, your a pro in your business !!! :)

And You are in yours i.e. Coding
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39962033
Did you know what was missing ??? I ran the code you provided first and the amended one and here are all the items you were missing not only the one you pointed out (I printed both and checked each item)

High                811.9000244      5/29/2012        808.13              811.9      799.07      801.43
Low                756.7299805      5/18/2012        760              781.2      756.73      774.1
High       1148.670044              9/8/2011      1        126            1148.67      1103.4      1140.83
Low              1012.099976              8/22/2011      1032.07            1049.33      1012.1      1041.33
High       1243                      7/26/2011      1236            1243              1182.3      1185.53
Low              1178.670044              7/12/2011      1208.2            1215.83      1178.67      1186.1

As far as the Ignore, I need to have more data with the Ignore to be able to test effectively. As you know better the outcome I would ask you to change the data so that you get some Ignore that falls on the current data captured so that when I modify and include the Ignore in the code you can then test and make sure the data has been excluded.

For sure we can do this in the scope of this question.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39962111
As I compared each point with chart from previous result step by step and I feel something wrong in any point then I stopped and raised question.

And after amendment same thing I done and I don't find any discrepancy at any point so I am totally ignorant about what point I am missing in previous results. Eagle eye ;)
Give me some time I am on my way back to home as soon as reach I'll provide you data.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39962163
Any view on this. Just side line question.

http://mobile.experts-exchange.com/Networking/Web_Computing/Q_28400008.html

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39962233
your new link sorry not my line of expertise.
Will wait for your file.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39962356
ok here it is, i had manually change data set which is reflect in result & it is in ignore row.

Thank You
Math-G-V06.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39962867
Not sure if I hit it !!! try this version I kept a copy of your original Data in Data (2) so you can compare.

run the macro in sheet Data.

Let me know.
gowflow
Math-G-V07.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39963603
I guess there is some differences between old & new point. I suggest to over come this. before old code start. add one more sub which delete the ignore rows. so I don't think after that we get any differences in points.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39964003
Deleting is not an option as when you do this with the kind of formula you have in Col G you get REF error in the 4 rows that are linked to the row that you delete.

My question for you: Are you sure the data is not correct ??? Remember when you ignore 1 row that says Ignore you actually have this row counted as a date but the values are not. You expected that all the other rows remain the same and not the red ones ???

Are you sure of the conclusion you made ? The only thing I am missing in my routine is that I did not modify the formula Evaluate(MIN ... and Evaluate(MAX ... maybe this one caused the error as when we exclude a row from a range we cannot use this same formula we need to adjust the formula so because it was long I preferred to give you the result  first and then see. My feeling is that  I need to modify this formula and then it will be fine.

pls advise
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39964086
Deleting is not an option as when you do this with the kind of formula you have in Col G you get REF error in the 4 rows that are linked to the row that you delete.

We can do one thing after Code apply copy formula from G7 & past till end. Delete ranges A:F which have ignore in column G. yes i see #Ref! error but no matter after deleting ignore rows purpose is solved. my concern is only that if ignore comes in Cell G7 & we delete, #Ref! comes in that cell which may affect procedure for next data set exploration.

For that my suggestion store ignore formula hard coded i.e. in code it self.

My question for you: Are you sure the data is not correct ??? Remember when you ignore 1 row that says Ignore you actually have this row counted as a date but the values are not. You expected that all the other rows remain the same and not the red ones ???

ignore means totally ignore that whole data - Date - Open High Low Close Volume (A:F).
Are you sure of the conclusion you made ? The only thing I am missing in my routine is that I did not modify the formula Evaluate(MIN ... and Evaluate(MAX ... maybe this one caused the error as when we exclude a row from a range we cannot use this same formula we need to adjust the formula so because it was long I preferred to give you the result  first and then see. My feeling is that  I need to modify this formula and then it will be fine.

up to you sir..... what ever easy for you.

Final result is must be exact same like we got from previous data in which i dint modified data manually.

if you want to see how result differ from previous one graphically. then use excel stock chart.

Thank You
0
 
LVL 8

Author Comment

by:itjockey
ID: 39964101
One more thing i noticed if we apply delete row option - Row O:W formula also results #N/A error. so you can add that formula also in code.

or you modify "formula Evaluate(MIN ... and Evaluate(MAX ... " which causes the problem.

it is your decision i am totally trusted you as Coding !!! which is not my expertise.


Thank You
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39964279
Well as a principle of work I am against deleting data !!!
this being said, I have done a workaround and twisted the code in a way to ignore the the Ignore rows.

Please check out the results and let me know.
gowlfow
Math-G-V08.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39965769
Seems working perfect. in this we have two additional points then previous one but no issues. Only thing is - There is two dates or say points mismatch - Old One (2/10/2011 & 6/6/2011) New One (2/9/2011 & 6/3/2011).
Points Mismatch
Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39966034
don't understand sorry can't do better.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39966056
I mean to say in previous result before Ignore Criteria amended in code there is Cell N51 have 2/10/2011 & Cell N44 have 6/6/2011.

After Amending Ignore criteria these two point have different data & different values.
i.e. 2/9/2011 & 6/3/2011.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39966058
May i ask next step of this Project?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39966293
no clue you tell me. For me I did what you asked now if there is something wrong pls let me know so I fix it.
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39966296
Ooops I just saw that you posted something let me look at it.,
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39966546
Well this is what I noticed and here again it is your Hit formula that is acting this way:

When I run the old routine on the same data and place a stop at the first Hit I get row 32 that shows a Hit on the Date 2/14/2011.

So the Interval to check for MIN / MAX is row 3 to 32 and it finds a Low on row 30 which is the following data

Low      974      2/10/2011      983.57      1008.67


Now when I run the new routine that Ignore the cells Ignore I get the first Hit at row 28 which is the Ignore row and I tell it take the next row ie row 29 so the data interval for MIN/MAX is row 3 to 27 and 29 to 29 (this is how it works to exclude row 28)

and in that interval the data that it finds a Low on row 29 which is:
Low      980.7000122      2/9/2011      1004.6      1025.33

So basically the question now is directed to YOU:

Why the Hit on the first instance is on row 32 and on the second instance is on row 28 (which is the Ignore row that we moved to the next Hit that is on row 29)

If you can answer this then we can understand why the data is different. Again here it is not the routine but your Hit formula that behaves Waco !!! :)

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39966606
I get back to you in 1 hour.
Thank you
0
 
LVL 8

Author Comment

by:itjockey
ID: 39966621
I will get back to you in 1 hour.
Thank you
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39966683
Well I think I found it !!! I modified both formulas in Col H and I to totally Ignore the Ignore rows. Basically what I did is the following:

Col H
If you encounter Ignore in this row or the previous or the next then put  a blank.

Col I
If you encounter Ignore in this row or the previous or the next the put the value of the previous row.

This way Ignore has no effect neither on the actual row or the previous or the next. I did this because I saw the your formulas in both Col H and I are dependents of current row next row or previous row so to cancel effect of the Ignore we carry over the data as from previous row that did not have any Ignore.

The new formulas are
Col H
=IF(OR(G2="Ignore",G1="Ignore",G3="Ignore"),H1,IFERROR(IF($L$3="High",IF($M$3-D2>H1,$M$3-D2,H1),IF($L$3="Low",IF(C2-$M$3>H1,C2-$M$3,H1),"")),0))

Col I
=IF(OR(G2="Ignore",G1="Ignore",G3="Ignore"),"",IFERROR(IF($L$3="High",IF(AND(H2>=H3,$M$3-(H2*61.8%)<C2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),IF($L$3="Low",IF(AND(H2>=H3,$M$3+(H2*61.8%)>D2,((CELL("ROW",A2))-(MATCH($N$3,A:A,0)))>10,(ABS($M$4-D2)/D2)>3%),"Hit",""),"")),""))

and I think I also did a small modification in the code and here is the new version altogether..

gowflow
Math-G-V08.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39966728
Let me check and get back to you.
Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39966876
Perfect !!!! may i ask new question?
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39966881
Thank You Very Much ..... You So Much Involve Than Me....    :)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39967247
Your welcome glad I could help. It always get my heart bigger and bigger when I know that I make a difference !
sure pls let me know what you need by posting a link in here.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39967359
May i post now?
0
 
LVL 8

Author Comment

by:itjockey
ID: 39967421
Here It Is The Link For New question "Score I"


Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39968944
Sir will pls look in to my new question?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39968947
Sir will you pls look on to my new question?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39985947
Sir as now above question is deleted so may I ask new one with proper wording?

Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now