# Maths2

Hi Expert,

I need to code this process which is mention in the attached Workbook pls help me out.
this only mathematical calculation but if I do manually it takes too much time.

Thanks
Maths-2.xlsx
LVL 8
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEOCommented:
Hi there,

Can you elaborate a bit more on your instructions? Like, a lot more? It's difficult at best to follow what you want.

There is different formulas for High & low
What does this mean? What are the different formulas? Where are they at? Does this mean anything for us?

Now find High & high date between date 2/11/2011 i.e. cell N3 (row 31)from date of very first hit which is in row 37. result will be in row 36 i.e. High 5589 2/18/2011
I think I can follow this. Still a little shaky on the details though. For example, are the dates in N3 and N4 a date range? Do you only want to find the next highest value after the date in N3? You could achieve this a couple of ways, either array-entered formula, which is confirmed with CTRL + SHIFT + ENTER, instead of just ENTER, but we'd need to know specifics. You could do this with a non-array entered formula as well, but again, more specifics are needed.

We need clear and concise explanations to give you any sort of help. Please list them out in a post and not in an attached file. In a file attachment we'd really only like to see two things, 1) the data as you have it to start, and 2) the desired outcome. All directions and instructions should be in a post.

Regards,
Zack Barresse
Senior ConsultantCommented:
Is this some kind of training or academic problem?

mlmcc
Hi Mr.Zack Barresse,

I know this is difficult to understand what is going on other minds i.e. my mind.as you said I had tried & mention below, what mi trying to do. please revert me back if need further clarification.

Step 1 By clicking button process start in Sheet “Data”
Step 2 if there is “High” in Cell L3 then have to find minimum value from column D from range - date in Cell N3 (which is in our case it is 1/4/2011 & it is in data table column A header ”Date” in row 4) to next Hit found.
How do I get “Hit” is calculated in attached workbook –sheet – A. See Column H & I i.e. Gap & Hit. Calculation starts from one row lower from actual one.as we find Hit, have to find minimum value  From range hit row number to start date row number from column D i.e.  Header “Low”. Calculation formula for High is mentioned in cell H5 & I5 Respectively.

As we found result in cell D31 which is minimum value in that range.

Step 3 Register this result in range L3:N3 by shifting one row down all data in that column  i.e. “Low” in cell L3 5175.10 in cell M3 & 2/11/2011 in cell N3.

Step 4 now there is “Low” in cell L3 so need to find Maximum value  from this low date to next hit date. Process is the same just formula is different & have to find Max from the range i.e. column C.

Step 3
Do this till we find “Hit” but there is no data in column A i.e. end of process part one.

Arrange result in column L M N in oldest date first to latest last copy & past special value in sheet “DataBase” from row 1 to next available blank column.

in short I am finding High Value & Low value from this table form available date to Hit date where ever I found.

in attached file there is Sheet Called “High” & “Low” you can see formula difference for High & low  criteria.
Sheet “Data” is desire result I want.
Sheet “DataBase” is final result.

See attached file.

Thank You
Naresh Patel
Maths-2.xlsx
Any one on this question?
Owner (Aidellio)Commented:
There is no way you'll get away with this as a formula.  Have you got the capability and skill to code a macro?
Nope
But it is clear now, what am I intend to do?
CEOCommented:
Naresh,

Sorry I didn't get back to you sooner, it's been busy. There is still some issues with your explanation not matching what you've said.

Before I get into this, have you ever thought of using a table? It might make this easier to work with.

Step 1 By clicking button process start in Sheet “Data”
I'm really not sure what I'm supposed to know about this step. What does this have to do with anything? Is this just a description of where you want a button to fire something off? Other than that I'm not sure what you're wanting done.

Step 2 if there is “High” in Cell L3 then have to find minimum value from column D from range - date in Cell N3 (which is in our case it is 1/4/2011 & it is in data table column A header ”Date” in row 4) to next Hit found.
Why would there be a "High' in cell L3? Do you list them there? When? How? Why? What if it isn't "High"? If I'm understanding this correctly, you will enter values in column L and N, either High or Low, and a date, and you want the corresponding high/low value returned from that date. If that is the case, the formula for that is easy and becomes...

``````=VLOOKUP(N3,\$A:\$F,LOOKUP(L3,{"Close","High","Low"},{5,3,4}),0)
``````

How do I get “Hit” is calculated in attached workbook –sheet – A. See Column H & I i.e. Gap & Hit. Calculation starts from one row lower from actual one.as we find Hit, have to find minimum value  From range hit row number to start date row number from column D i.e.  Header “Low”. Calculation formula for High is mentioned in cell H5 & I5 Respectively.

This doesn't make sense.  Why not keep this as a single formula? Will the date column always be sorted in ascending order? Will this data always stay the same? On your High and Low sheets it doesn't match what you have in columns L through N on your Data sheet. Why is that?

I'm not sure if it's the language barrier, but I find almost nothing helpful about your explanation. The attached file uses a table on the Data sheet and incorporates your formulas from both sheets High and Low. Honestly I'm not sure what else you need here.

Regards,
Zack
Maths-2.xlsx
Give me some time to reply.......i am off the desk ......

Thanks
Mr.Zack,

Sorry for my improper & confusing explanation. This is my last attempt to show how we achieve this. Now there is only one sheet on which we have to work on. “Data” Sheet. I had mention logic flow behind this by clicking button “Find”. One more thing there is manually typed data in rang L3:N3 for one time. So before clicking button there is data available in range L3:N3.

Logic Flow

## 01. Button Click

On click of button process start.

## 02. Find Row Number Of Date

Find row number of the date  in column A which is mention in Cell N3.

## 03. Putting Formula - Column H

Put formula in column H Row is Step 2 Result +1.“IF(\$L\$3="High",IF(\$M\$3-D5>H4,\$M\$3-D5,H4),IF(\$L\$3="Low",IF(C5-\$M\$3>H4,C5-\$M\$3,H4),""))”.(assume date found in row 4) if date found in row 10 then formula will be in H column row 11(Cell H11) is “IF(\$L\$3="High",IF(\$M\$3-D11>H10,\$M\$3-D11,H10),IF(\$L\$3="Low",IF(C11-\$M\$3>H10,C11-\$M\$3,H10),""))”.

## 04. Putting Formula - Column I

Put formula in column I row is Step2 row number +1. I.e. Row 5(Cell I5) assumes date found in row 4.” IF(\$L\$3="High",IF(AND(H5>=H10,\$M\$3-(H5*61.8%)<C5),"Hit",""),IF(\$L\$3="Low",IF(AND(H5>=H10,\$M\$3+(H5*61.8%)>D5),"Hit",""),""))” if date found in row 10 then formula will be “IF(\$L\$3="High",IF(AND(H11>=H16,\$M\$3-(H11*61.8%)<C11),"Hit",""),IF(\$L\$3="Low",IF(AND(H11>=H16,\$M\$3+(H11*61.8%)>D11),"Hit",""),""))” in Cell I11.

## 05. DropDown Of Formula

Dropdown formula till end.

## 06. Finding Text Hit In column I

Find Very first Hit Found in column I.

## 07. Part A If there is L3=High

if L3=”High” then Find lowest value from column D – Range is first row number from where we found date (Step 2) to (Step 6).

## 07. Part B If There is L3=Low

if L3=”Low” then Find Highest  value from column C – Range is first row number from where we found date (Step 2) to (Step 6).

## 08. Arrangement To Put New Result

Shift one row down data - range L3:N3.

## 09. Registering New Result

Copy Step 7 value & past to M3

## 10. Registering New Result Date

Copy past Step 7 corresponding date in N3

## 11. Registering High Or Low

.if L4=”High” then type “Low” in L3 & if L4=”Low” then type “High” in L3.(As we shifted one row down old data).

## 12. Clear Data & Formula

Clear data & formulas in column H & I

## 13. Next i

Step2 till Column A=””

## 14. End

Maths-2.xlsx
Math2.png
Regarding Tables - I know but I am not much familiar with it.

Thanks
CEOCommented:
Hi,

Thanks for the detailed steps. Did you look at the file I uploaded? If not, take a look. I'll go over the steps in more detail. I don't know if the file I uploaded will help you or not.

What I'm wondering is, the range from L3:Nx, where x is an unkown number of rows, or the last row with data (row 1 in the latest sample file, row 8 in your previous sample file), you're solving for column M, is that right? And with each new result, it should get written to columns L through N, one row per iteration? When should it stop? When no more "Hit" values are found?

Zack
Mr.Zack,
You stop at where there  is no data in column A.  Or say range A:E. So last formula drop down (step 5) till last data available in range A:E.

Thanks
Even in step 13 I had mention.

Thanks
Mr.Zack,

Any Luck..?
Mr.Zack,

You are working on this?

Thanks
CEOCommented:
I was busy today, but yes I looked at it. It's a little difficult to understand you though. You say...
You stop at where there  is no data in column A.  Or say range A:E. So last formula drop down (step 5) till last data available in range A:E.
But I'm still uncertain as to what that actually means. It doesn't make sense.

You stop at where there is no data in column A.
What do you mean? What is stopped? What data?

Or say range A:E. So last formula drop down (step 5) till last data available in range A:E.
I'm still lost with this. What formula, and what drop down? And what is the last data available in range? The last row of the table? What is the cell address you're talking about?

I'm afraid I can't really give you anymore unless I understand what you're talking about. If you can refer to specific ranges in your explanations it would help. I'm sorry, I keep trying to follow you but it's proving difficult.

Zack
Mr.Zack,

I like it 1st understand & then execute, no  problem ask your confusion as many time as you want.

Thank You
Mr. Zack,

You are still on this question?

Thanks
Owner (Aidellio)Commented:
This needs to be done via a macro.  Have you coded anything yourself yet?

I would start by doing it manually and record a macro as you do it.  That should give you enough code to start with and I can help you loop it once that's done
Do u want me record macro?...if yes let me try as per my understanding.....
Owner (Aidellio)Commented:
Sir it is out of my reach & understanding - My first preference is to do it by formula but I think it not possible via formula & I assume that  Coding is the only way out ..I don't have much knowledge in coding. So it is up to you - tell me what i have to  do to achieve this?

Thanks
i had tried twice & thrice - again & again but no result - it is like circular reference. i don't know how to deal with.
Owner (Aidellio)Commented:
That's ok. It's late here so I'll have to look tomorrow if I can
Sure eagerly looking forward. but take my this Comment for conclusion.
Owner (Aidellio)Commented:
ok take a look as I think i've got it but I'm not sure of the values

Press the find button to see the values copied and pushed down.

Click the reset to take you back to just one row of values
Maths-2.xlsm
Sir sure I will, i am on my way back to home.give me some time to test.

Thanks
Sir ....I guess we missed

## 12. Clear Data & Formula

Clear Data & Formulas in Column H & IThat is why there is to many points.

Thanks
Owner (Aidellio)Commented:
Do you mean after its finished to just clear the gap and hit columns?
Yes Sir...& again start from step 2.     Sorry For delay in reply.

thanks
Owner (Aidellio)Commented:
Ah please have another look as i specifically did it so you didn't need to re apply the formulas. They calculate with each iteration
Owner (Aidellio)Commented:
I shift the old values down and write the date, value and alternate either "Low" or "High"
The formulas are all calculated before they people's repeats.
Sir Tagit,

but if we don't clear formulas then values i.e. result will defer from actual one. so after clearing have to put again formula in as per Step 2. or if you want to formulas be intact. after Step 11 - Step 2 find row number say 8 then 8-1=7, delete rows Range A3:F7 & shift data up. So after this Step 3 4  5 12 is not required .

Getting my point?

Thanks
Owner (Aidellio)Commented:
No I don't but I'm not at my computer to test out what saying so I'll be in touch when I get there.
Maybe if you explain how you came up with the formulas it would make more sense.
From your steps above you've indicated that you just clear the formulas and put them back in after changing the date and values etc in L3:N3. If that's the case then my code should work as the formulas recalculate each time
I don't mind, if include these formula in code it self.

Before explanation  just one question - Do u know any thing about technical analysis. (Stock Market).

Thanks
if yes then I ll explain via that way or just like formulas.
Owner (Aidellio)Commented:
No I'm sorry,  I don't but don't need to explain why you use a particular percentage but what the formula needs  to be and what it needs to include.
Ok got it but FYI 61.80% is Golden Ratio.

For High

IF Cell L3 = High then we are finding Lowest Low. What I did is From High i.e. M3 minus Lows of each row (Column D). & added one criteria if above value is greater than current result then above value else current value. i.e. =IF(\$M\$3-D5>H4,\$M\$3-D5,H4)

& For HIT
there is 2 Criteria to match-  Criteria 1 Gap i.e. Current example Cell H5 is greater than or equal to forward 5 rows Gap i.e. Cell H10. Criteria 2 61.80% of Gap value – High point (Cell M3) is lesser High (Cell C5) then Hit else “”.
Both are matched then "Hit" else "" i.e. =IF(AND(H5>=H10,\$M\$3-(H5*61.8%)<C5),"Hit","").

For Low

Now For Low if Cell L3=Low then finding highest high from range between low to next hit. Formula flow is like this for gap - current high (column C) - Low  (Cell M3). Here also I had add criteria previous value is greater than this value then previous value else this value. i.e. =IF(C5-\$M\$3>H4,C5-\$M\$3,H4).

For Hit two - criteria 1 forward 5 row value is greater than or equal to. Criteria 2 61.80% of Gap plus Lowest point is greater than Low (column D). i.e. =IF(AND(H5>=H10,\$M\$3+(H5*61.8%)>D5),"Hit","")

Formula Merge

And I had combine this formula if high then this if low then this else ""
For both Gap & Hit column
=IF(\$L\$3="High",IF(\$M\$3-D5>H4,\$M\$3-D5,H4),IF(\$L\$3="Low",IF(C5-\$M\$3>H4,C5-\$M\$3,H4),""))
=IF(\$L\$3="High",IF(AND(H5>=H10,\$M\$3-(H5*61.8%)<C5),"Hit",""),IF(\$L\$3="Low",IF(AND(H5>=H10,\$M\$3+(H5*61.8%)>D5),"Hit",""),""))

I had attached example file in which there is sheet High & Low to see formula difference. that only for understanding only. formula merge file is math 2 in which you are working on it.

Thanks
Explanation.xlsx
Owner (Aidellio)Commented:
Thanks for the background.  It certainly helps.

Are you saying that the formulas need to cleared and entered again from the row where the "hit" was last found?
Owner (Aidellio)Commented:
Ok, I clear the Gap function down to the row the hit was found before continuing and it returns less values so it would be affecting the results.  the Hit formula has nothing to do with the rows above so it doesn't need to be cleared each time
Are you saying that the formulas need to cleared and entered again
Yes
from the row where the "hit" was last found?
No
it is at =MATCH(N3,A:A,0)+1 row number in Column H & I
Owner (Aidellio)Commented:
So in other words. Clear the formula up to and including the date in N3, found in column A
yes  :)
Owner (Aidellio)Commented:
Ok! I'll sleep on it now as it's late.  :)
10:30 I guess ...good night...
Owner (Aidellio)Commented:
Good morning :)

I just need to clarify about the "Hit" and the second and subsequent iterations.

Is the RANGE the rows between the Date found and the "Hit"?
Owner (Aidellio)Commented:
And how do you know the next Date to find (Step 2)?
Owner (Aidellio)Commented:
I think we're almost there! :)
Good Morning & Good Evening  :)
Yes Second iteration is Range the row between the date found & the Hit ...

Thanks
SO9.xlsx
Sorry wrongly attached ...pls ignore...
Owner (Aidellio)Commented:
In Other words the new date for n3 is the corresponding row of the hit found.  Got it!
Won't be long now :)
not like that it is between lowest or highest values as per criteria between found date row & hit raw.
i.e. if we calculating from High (Cell L3 = High) for Low then we are finding Lowest value from column D range, Range =  found date row number to Hit row number & if we are calculating Low(Cell L3 =Low) then we finding Highest  value from column C - range Found date row to hit row number.

Thanks
Math2.png
Owner (Aidellio)Commented:
Yes that makes sense.  But what is the next date we search for?  the one on the same row as hit?
no it is in the row where we found lowest or highest value from column C or D.
See the blue line in attached screen shot Step 10
sorry sorry    I had written right :) sorry
Owner (Aidellio)Commented:
That's fine. Got it now. Sorry about so many questions. I just have to modify the macro slightly to work out the corresponding date of the high/low as I had it as the corresponding date for the "hit"
Brb
No Problemo, ask any number of question.....
just need to know in your new code which is you are working on it - it includes Gap & Hit column in Code it self or it is in worksheet...just asking both way I don't mind....

Thanks
Owner (Aidellio)Commented:
The gap and hit columns are in the worksheet though I clear the formulas as I work through each iteration.  The formula itself is stored in the code.  Once we've got this working, have a look at the macro and I'll answer any questions you have
Owner (Aidellio)Commented:
Ok I'm getting an infinite loop as the date and hit are on the same line once I get to line 326.

I'd actually like to look at this another way.  I'd like you to look at this macro as it details the flow.  There are other functions but they're just simplifying the Calculate Sub so it's easier to read.  Let me know what you don't understand.

``````Sub Calculate(Optional ByVal iDateRow As Integer = 3)
Dim curRow, curHit As Integer
Dim newVal As Single
Dim newRow As Integer

' find the date from N3 in Col A
curRow = FindDate(iDateRow)

' If we find a date
If (curRow <> 0) Then
'put the formulas here and fill down
Range("A3").Select
Selection.End(xlDown).Select
Dim lastRow As Integer
lastRow = ActiveCell.Row

WriteFormulas curRow, lastRow

Application.Calculate
Else
' Exit if date could not be found
Exit Sub
End If

curHit = FindHit(curRow)

If (curHit <> 0) Then
Select Case Range("L3").Value
Case "High"
newVal = Evaluate("=MIN(D" & curRow & ":D" & curHit & ")")
newRow = FindMaxMinRow(curRow, curHit, "D", False)
newVal = Range("D" & newRow).Value
Range("L3").Value = "Low"
Range("M3").Value = newVal
Range("N3").Value = Range("A" & newRow).Value
Case "Low"
newVal = Evaluate("=MAX(C" & curRow & ":C" & curHit & ")")
newRow = FindMaxMinRow(curRow, curHit, "C", True)
newVal = Range("C" & newRow).Value
Range("L3").Value = "High"
Range("M3").Value = newVal
Range("N3").Value = Range("A" & newRow).Value
End Select
'        If (MsgBox("Ok for Next or Cancel", vbOKCancel, "Step by Step") = vbCancel) Then
'            Exit Sub
'        End If
'Range(Cells(3, 8), Cells(curRow, 8)).Clear
Cells(curRow, 1).Select
Application.Calculate
Calculate curRow
Else
'could not find a hit so finish
MsgBox "Finished"
Range("L3").Select
End If

End Sub
``````
Maths-2.xlsm

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Sir rob,
Give me some time to check, as I am on way back to home,posting this comment via mobile.

Thanks
Sir rob,
What I asked for is perfectly match with your Code.row 326 - I saw there is freak trade I need to overcome with this kind of situation and rethink  different kind of formula. As manually change data and go on but it still come up with infinite in one stage where market is side ways I know may be you don't understand what I am talking.so as far as this question, this is solved.

Thank you very much for your corporation really appreciated. I guess we will meet soon on EE again if you wish too.