# Need average days between transactions

#### MrChuckles

##### New Member
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### barry houdini

##### MrExcel MVP
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
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
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
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.

Replies
1
Views
564
Replies
18
Views
673
Replies
2
Views
660
Replies
0
Views
1K
Replies
1
Views
60

1,171,268
Messages
5,874,738
Members
433,070
Latest member
omartarek

### 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.

### Which adblocker are you using?

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

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