CODEWITHSUNDEEP
exceldatetimeexcel-formulaaverage
David Allen
David Allen

Reputation: 1163

Average time of Repetition

Hi I have a Excel sheet with a list of every job that we have been to with dates, lastname, postcode. A lot of our business is repeat work so the same customer will appear on the table multiple time but with different dates of-course.

What I wish to work out is column D the average time between Repetition for each customer in each row.

Hope you guys can help.

Thanks

     A             B            C           D
  Date of Job   Surname     Postcode    Repetition
  07/02/2013    Smith       CV32 6LP      4.3
  06/02/2013    Taylor      CV3 2UD       5.6
  06/02/2013    Green       B9 5XU        6.7
  ......        .....       .......

Upvotes: 0

Views: 89

Answers (1)

barry houdini
barry houdini

Reputation: 46331

If you want an average number of days between each occurence for each postcode then you could get that by finding the earliest date, subtracted from the latest date and divided by the number of occurences (minus 1), so assuming dates in A2:A100, surnames in B2:B100 then in D2 try this "array formula"

=IFERROR((MAX(IF(C$2:C$100=C2,A$2:A$100))-MIN(IF(C$2:C$100=C2,A$2:A$100)))/(COUNTIF(C$2:C$100,C2)-1),"Only one order")

confirmed with CTRL+SHIFT+ENTER

I'm assuming you can differentiate by postcode alone....

Upvotes: 2

Related Questions