I'd like a bit of help for a scenario I'll be working on with Excel. Any bit of help will be highly appreciated.
I will have an ever growing range of dates for specific items and I need to make an Excel sheet to determine the average number of days between them per item. Basically, this is a simplified example on how I plan to tabulate the data:
Item Code | Date
A.ITEM | January 15, 2017
B.ITEM | January 16, 2017
A.ITEM | January 22, 2017
C.ITEM | January 25, 2017
A.ITEM | January 31, 2017
C.ITEM | February 2, 2017
B.ITEM | February 12, 2017
B.ITEM | February 24, 2017
C.ITEM | March 7, 2017I will then create another table that will display the average duration between the dates per item. I imagine it will look like this:
Item Code | Average Life Span
A.ITEM | 9 days
B.ITEM | 20.5 days
C.ITEM | 21.5 daysWhat formula would I need to make the second table possible? I've been racking my brain for a while now and, because I'm not very familiar on Date functions in Excel, I still don't know how. Is it even possible?
Thank you!
82 Answers
Note that the average of the differences is just (max-min)/count: (d1-d2) + (d2-d3) + (d3-d4) +... = d1-dn
With that, you can use a formula like(MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)
This would put all the dates in one pot, though, so you need to additionally filter by your codes - instead of simply MAX(d1:dn), use MAX(IF(a1:an=code,d1:dn,0) as a matrix formula. Add similar ifs for MIN and COUNT (or use COUNTIF); note that for MIN, the else-value can't be 0, but needs to be something very large.
In this example, Item Code is in Column A, Date is Column B, and then I add new data. Row 1 is headings.
First, you'll need to sort your data each time you add a new row, with a two layered sort:
- first by Item Code
- second by Date
Then, in the column to the right of Date add a Time Between calculation and drag/fill down:
=IF(A2=A1,B2-B1,"")Then to average the times. List the Item codes A, B, C elsewhere - I put them in column F in my test. Next to A, enter and drag/fill down:
=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)I got:
- A: 8
- B: 19.5
- C: 20.5
(for A: 22 Jan - 15 Jan is 7 days, 31 Jan - 22 Jan is 9 days, average is 8 days)
3
