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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,540
Messages
5,511,928
Members
408,868
Latest member
AndrewLeonard

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top