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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,133,027
Messages
5,656,475
Members
418,306
Latest member
makis1023

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top