Need average days between transactions

MrChuckles

New Member
Joined
Nov 23, 2013
Messages
5
I have a table of sales with fields for salesperson ID, date key, and sales amount. Each salesperson may occur between 1 and 100 times in the table depending on whether she sold one order or 100. I am trying to calculate the average duration in days between each sale. For example assume a salesperson has a sale on January 1 and another on January 10 (9 days after the first) and another on January 31st (21 days after the second). The average time between sales would be 15 or (9+21)/2.

I also have a related date table and have successfully calculated the days between the first and last date for each sales person. I'm getting stuck on getting the average of all the date durations in the middle. Any ideas?



I'm using Excel 2010 and the corresponding PowerPivot.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I also have a related date table and have successfully calculated the days between the first and last date for each sales person.
Hello MrChuckles, welcome to MrExcel

Isn't the average just the duration between first and last date (which you already have) divided by the number of sales for that salesperson (minus 1)?. In which case you can just divide the total days by the number of sales (-1), which you can presumably get with a COUNTIF function on the salesperson ID column, e.g.

=days/(COUNTIF(Salespersons,Z2)-1)

where Z2 contains the Id for that specific salesperson
 

MrChuckles

New Member
Joined
Nov 23, 2013
Messages
5
Hello MrChuckles, welcome to MrExcel

Isn't the average just the duration between first and last date (which you already have) divided by the number of sales for that salesperson (minus 1)?. In which case you can just divide the total days by the number of sales (-1), which you can presumably get with a COUNTIF function on the salesperson ID column, e.g.

=days/(COUNTIF(Salespersons,Z2)-1)

where Z2 contains the Id for that specific salesperson
Hi Barry,

I believe that would be true if the intervals were all equal. If we take an extreme example though it gives different results with uneven intervals. Assume four orders. One on 1/1, another on 1/5, another on 1/10, and the last on 12/31. An average of the start and end date would be 182 (364 / 2). An average of the actual intervals would be 121 (intervals (4 + 4 + 355) / 2). Please correct any of that if it is in error.

I appreciate the help Sir. I am really glad I found the board. I have Bill's book as well as several others but am very new to PowerPivot. The people here rule.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Isn't it the same either way?

For your example you are averaging 3 intervals of 4, 5 and 355 (assuming a non leap year) so the result is 364/3 = 121.33

I'm suggesting you just do this

=(MaxDate-MinDate)/(Sales-1)

=364/3 = 121.33
 

MrChuckles

New Member
Joined
Nov 23, 2013
Messages
5
You are right Barry. Thanks. I had missed the point about dividing by the intervals in my first example. Your answer fixed me up. I appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,690
Messages
5,488,285
Members
407,633
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top