The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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

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

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.

Math2.png

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:N

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

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

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

Thanks

Sure eagerly looking forward. but take my this Comment for conclusion.

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

Thanks

The formulas are all calculated before they people's repeats.

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

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

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

Thanks

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","").

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","")

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-D 5,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",I F(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

Are you saying that the formulas need to cleared and entered again from the row where the "hit" was last found?

Are you saying that the formulas need to cleared and entered againYes

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

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"?

Yes Second iteration is Range the row between the date found & the Hit ...

Thanks

SO9.xlsx

Won't be long now :)

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

Brb

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

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
CopyDataDown
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
CopyDataDown
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

Your issues matter to us.

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

Start your 7-day free trialGive me some time to check, as I am on way back to home,posting this comment via mobile.

Thanks

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.

I have come with change in Hit formula.. if you wish to solve it pls see my this question Math3.

Thank You

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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

What does this mean? What are the different formulas? Where are they at? Does this mean anything for us?

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