I have observed inventory and I have a dump of all purchase information. I would love a formula or a combination of tricks and formula to compute this. I know very little about VB or Macro's but I am open to anything at this point. The yellow area is where I believe I would like the formula to go. I provided one check figure for this example. Thank you!
Not sure I totally understand your question, but the following formula in I4, then dragged down, will give a sum of all quantities purchased for that product number.
Formula:
Please Login or Register to view this content.
Acts 4:12
Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.
So in H4 I see I have 111 items for that product number. Assuming I sell the oldest item first. How much did the last 111 items cost me? That could be what remains from the last purchase or the 4 last purchases. All were purchased at different unit costs each time so you know if the formula works or not when you check the math totaling the most recent purchases.
I want the correct total value of the inventory assuming I sold the oldest item first for each product number.
Will your columns A:D are normally be sorted by date? Does the code need to do a sort before doing the calculations, or would that cause a problem?
I don't think it matters if it sorts it. If your solution requires it to be sorted to get the right date order then I would sort. It would be good to not rely on the order to be in correct date order if your spreadsheet needs that.
If you're needing FIFO, then the sort is needed. In your example, you had a test result of $558, but sorted by date, the actual value is $530 (unless my code isn't right). Give this a try.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks