# Excel, Average dates between transactions?

I have an inventory data set that I am playing with and I have hit a wall in trying to figure out something that I want to do.

The data set has a name column (name of item; e.g., “hammer” or “saw”), a count column (how many are in stock), and a transaction time column. (And there are other columns, including transaction time broken down into day, month, year, time, and day of the week.)

For each unique inventory item name, I want to find the average days between

• purchase to purchase,
• purchase to sale, and
• sale to sale.

Transactions can be identified as purchases or sales by whether the inventory (“count”) increases or decreases relative to the previous transaction for the same item.

-
To stay fixed on the choice of Excel seems to make the solution harder to create than using a programming/query language directly at the source. What is the Excel file generated from? – gogowitsch Feb 5 '13 at 19:49
Okay so it isn't so easy to do as I had thought. It was originally a CSV file, which I am sure was from a mySQL database. – Arrak Feb 5 '13 at 19:54
Can you query the MySQL? If yes, please ask another question containing the DB layout at Stackoverflow instead of Super User. It can certainly be done in Excel, though, especially if VBA is an option. Just wait and see what the experts come up with! – gogowitsch Feb 5 '13 at 20:00

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 `AE8-AE2` (i.e., `C10-C4`).
• 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.
-