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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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,101,934
Messages
5,483,774
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top