data averages following If/then in excel

[Nazzo07]

For the attached excel example i am looking to have a script that will take the truncated time info from column L and average it under certain circumstances.

For each occurance of data type 3 in column D, use the data from the next occurance of data 12 (ie: L3-L2 then L10-L9, noting that L64-L61).

Next on the same token; For each occurance of data type 12, use the data from the next data type 13 (ie: L8-L3 then L15-L10)

Any help would be greatly appreciated and any additional info needed please ask.

Thanks

[Cutter]

Hello Nazzo07, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[goosnargh]

If I understand what you want, I have what you want. In the workbook attached, column M is a list of time values for the information in column L to facilitate the calculations. Column N has the data for data types 3 and 12. Column O has the data for types 12 and 13. The entries on the first rows of these columns are a count of how many data points there will be, and the rest of the entries are the time differences. Row 2 of column N is the difference between the time associated with the first occurrence of data type 3 and the next of type 12. The last row of column N is the difference between the final occurrence of data type 3 and the next of type 12. Similar idea with column O.

This is what the code to generate the values for the N column looks like:

Sub ThreeAndTwelve()
Dim rowlookup3 As Long
Dim rowlookup12 As Long

Cells(1, 1).Select
' cell(1,14) contains a count of how often 3 occurs
For Count = 1 To Cells(1, 14).Value
  Cells.Find(What:="3", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select
       rowlookup3 = ActiveCell.Row
        time1 = Cells(rowlookup3, 13).Value
    rowlookup12 = Cells.Find(What:="12", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
    time2 = Cells(rowlookup12, 13).Value
   answer = time2 - time1
    Cells(Count + 1, 14).Value = answer
   Next Count
   End Sub

I hope this is what you're looking for.

[Nazzo07]

Thank you! This works perfectly in the example you sent. Unfortunately I can not get the macro to run in my full data sheet of compiled monthly data. I am only trying to get it running on 1 sheet for now. I tell it to run and it sits and stares at me blankly. Any advice on how to finish this up?

[zbor]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc