Forum Discussion
Inventory Formula
| BEG. INV (CTN,BDL) | BEG. INV (BAG,PCS) | REMAINING (CTN,BDL) | REMAINING (BAG,PCS) |
| 27 | 15 | 27 | -3 |
Hi I was wondering if what formula to use or if there is any for what I want to happen when using template for inventory.
My beginning inventory in 27 ctns and 15 bags. Each carton has 20 bags. When 15 bags gets to 0, is there a way for the 27 cartons to be 26 and the bags be 20?
3 Replies
Perhaps like this:
The initial inventory is in A2 and B2, and the number of bags taken is in C2.
The current inventory is in D2 and E2. The formula used is
=LET(Remaining, G2*A2+B2-C2, HSTACK(QUOTIENT(Remaining-1, G2), MOD(Remaining-1, G2)+1))
Thanks. Is there an alternative formula that I can use? I don't have hstack on my excel.
In D2:
=QUOTIENT(G2*A2+B2-C2-1, G2)
In E2:
=MOD(G2*A2+B2-C2-1, G2)+1
