
Excel is probably not the best way to do this, but I managed it.
Here’s some sample data I made up, and the results of my formulae:
(The image above is a link to a larger image, which shows the hidden helper columns.)
I assumed the existence of a Date column that was of an acceptable level of granularity.
Here are the formulae:
AA2: =VLOOKUP($A2, $A3:$C$22, 2, FALSE) * Note: $A3 , not $A2 .
AB2: =$AA2$B2
AC2: =VLOOKUP($A2, $A3:$C$22, 3, FALSE)
AD2: =IF($AB2>0, $A2, "")
AE2: =IF($AB2>0, $AC2, "")
AF2: =IF($AB2<0, $A2, "")
AG2: =IF($AB2<0, $AC2, "")
AH2: =IF($AB2>0, IFERROR(VLOOKUP($AD2, $AD3:$AE$22, 2, FALSE), ""), "")
AI2: =IF($AB2>0, IFERROR($AH2$AC2,""), "")
AJ2: =IF($AB2>0, IFERROR(VLOOKUP($AD2, $AF3:$AG$22, 2, FALSE), ""), "")
AK2: =IF($AB2>0, IFERROR($AJ2$AC2,""), "")
AL2: =IF($AB2<0, IFERROR(VLOOKUP($AF2, $AF3:$AG$22, 2, FALSE), ""), "")
AM2: =IF($AB2<0, IFERROR($AL2$AC2,""), "")
X2: =AVERAGEIFS($AI$2:$AI$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, ">0")
Y2: =AVERAGEIFS($AK$2:$AK$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, ">0")
Z2: =AVERAGEIFS($AM$2:$AM$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, "<0")
I assume that you have some way of populating the W column with the unique inventory item names.
22 represents the last row that has data.
Explanation:
 The pink columns,
AA:AC , map to the next transaction for the item identified in columns A:C of the current row. For example, AA2:AC2 map to A4:C4 . The Delta column shows the change in inventory between those two transactions. You could just define AB2 to be =VLOOKUP($A2, $A3:$C$22, 2, FALSE)  $B2 and delete column AA ; I believe that this way is clearer.
 The orange columns (
AD:AG ) break the transaction pairs from columns AA:AC into purchases (AD:AE ) and sales (AF:AG ), based on whether the inventory delta is positive or negative, and replicate the item name from column A .
 The light green columns (
AH:AI ) map each purchase to the next purchase
of the same item and compute the number of days between them. So, for example,
AH2 is AE8 (which is C10 ), and AI2 is AE8AE2
(i.e., C10C4 ).
 The darker green columns (
AJ:AK ) map each purchase to the next sale of the same item and compute the number of days between them.
 The blue columns (
AL:AM ) map each sale to the next sale of the same item and compute the number of days between them.

answered Feb 7 '13 at 3:27

