Excel- Average days between group of dates

Question

I'm trying to use excel to calculate the average frequency of delivery for a set of parts. I have a data set that has two columns- part number and delivery date. I'm trying to figrue out out oftne parts get delivered, on average, in terms of days. I tried using nested ifs like averageif(a2=a2:b9999,datedif(xx)) etc, but to no avail. I'm looking for this:

Input:

Part A    8.1
Part A    8.8
Part A    8.15

Output: Part A Average Delivery - Every 7 Days

etc etc. Any ideas?

Sort by Part number then date (asc), then add a column (formula starting in C2, or C3 if you have headers, then fill down) =IF(A2=A1,B2-B1,"") Now column C has the #of days between each delivery, so you can summarize that (eg use a pivot table) — Tim Williams

pnuts pnuts · Accepted Answer · 2013-08-26T18:27:49

If your dates are in ColumnB:

=(MAX(B:B)-MIN(B:B)--1)/COUNT(B:B)

or:

=(MAX(B:B)+1-MIN(B:B))/COUNTA(B:B)

should serve.

Edit

If you have multiple parts (the above assumed only one) and the list is in no particular order then a PivotTable may be best (say with its top left-hand corner in D1), in Tabular form with Part for Row Labels and Delivery three times for Σ Values (the first as MAX, the second as MIN and the third as COUNT). Then =(1+E3-F3)/G3 copied down should give you the average bumber of days between deliveries. For example 5 in your example (3 deliveries in 15 days).

Excel- Average days between group of dates

1 Answers