Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA calculate avg dates
In column A I have the Customer Number, Column B = Item Number, Column
J = Dates. I am trying to find a quick way to calculate the average days between orders for the same item for the same customer. I was trying to put it into a formula and then paste into teh right cells. This takes a very long time. Thanks, Jay Sub AvgThisItem() Application.ScreenUpdating = False Dim Rng As Range Dim i Dim Lstrow As Long Dim Fn As String Lstrow = Range("A6").End(xlDown).Row Set Rng = Range("B6", Range("B6").End(xlDown)) Range("K5") = "Days" Range("L5") = "Avg Days" Range("N1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _ Lstrow & "=$A1)*(1))" Range("O1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _ Lstrow & "=$A1)*($K$6:$k$" & Lstrow & "))" Range("K1") = "=ABS(IF(B1=B2,J1-J2,0))" Range("K1:O1").Copy Range("K6:K" & Lstrow).PasteSpecial xlPasteFormulas For Each i In Rng If i < i.Offset(-1, 0) Then Range("L" & i.Row).Value = "=O" & i.Row & "/n" & i.Row Else End If Next i End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA calculate avg dates
Jay,
Arrange your data all by colums A, B and J (here in ascending order) first. You will get another view right away. -- Regards Petr Bezucha "jlclyde" wrote: In column A I have the Customer Number, Column B = Item Number, Column J = Dates. I am trying to find a quick way to calculate the average days between orders for the same item for the same customer. I was trying to put it into a formula and then paste into teh right cells. This takes a very long time. Thanks, Jay Sub AvgThisItem() Application.ScreenUpdating = False Dim Rng As Range Dim i Dim Lstrow As Long Dim Fn As String Lstrow = Range("A6").End(xlDown).Row Set Rng = Range("B6", Range("B6").End(xlDown)) Range("K5") = "Days" Range("L5") = "Avg Days" Range("N1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _ Lstrow & "=$A1)*(1))" Range("O1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _ Lstrow & "=$A1)*($K$6:$k$" & Lstrow & "))" Range("K1") = "=ABS(IF(B1=B2,J1-J2,0))" Range("K1:O1").Copy Range("K6:K" & Lstrow).PasteSpecial xlPasteFormulas For Each i In Rng If i < i.Offset(-1, 0) Then Range("L" & i.Row).Value = "=O" & i.Row & "/n" & i.Row Else End If Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I calculate dates? | New Users to Excel | |||
Calculate dates | Excel Worksheet Functions | |||
calculate dates | Excel Discussion (Misc queries) | |||
How do I calculate an age from 2 dates? | Excel Worksheet Functions | |||
How do I calculate dates? | Excel Worksheet Functions |