Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.