How to create an Inventory Stock Balance for each row in Sheets with the help of array-formulas.
Sample:
| Item | Operation | Quantity | Balance (Formula) |
|---|---|---|---|
| a | buy | 500 | 500 |
| b | buy | 100 | 100 |
| a | sell | 110 | 390 |
| a | buy | 1000 | 1390 |
| a | sell | 800 | 590 |
| b | buy | 200 | 300 |
| a | sell | 390 | 200 |
| b | sell | 50 | 250 |
| b | sell | 150 | 100 |
The desired result is the balance for each row, the balance for the current stock item.
Try this formula-
=INDEX(BYROW(A2:A,LAMBDA(x,IF(x="",,SUM(FILTER(C2:C,A2:A=x,B2:B="buy",ROW(A2:A)<=ROW(x))))))-BYROW(A2:A,LAMBDA(x,IF(x="",,SUM(IFERROR(FILTER(C2:C,A2:A=x,B2:B="sell",ROW(A2:A)<=ROW(X))),0)))))
If you want to omit zero 0 values then use-
=BYROW(A2:A,LAMBDA(z,IF(z="",,INDEX(BYROW(A2:A,LAMBDA(x,IF(x="",,SUM(FILTER(C2:C,A2:A=x,B2:B="buy",ROW(A2:A)<=ROW(x))))))-BYROW(A2:A,LAMBDA(x,IF(x="",,SUM(IFERROR(FILTER(C2:C,A2:A=x,B2:B="sell",ROW(A2:A)<=ROW(X))),0)))),ROW(z)-1))))
Please see sample file.
You can use the new functions:
LAMBDA + SCAN + BYROW
Here's the function to get inventory for all items as a string:
=SCAN("",lambda(item,op,s,mbuy, INDEX(if(item="",,SUBSTITUTE(item," ","_")&""& IF(op=mbuy,1,-1)*s)))(A4:A12,B4:B12,C4:C12,"buy"),LAMBDA(ini,s,if(s="",ini,
SUBSTITUTE(QUERY(BYROW(QUERY({if(ini="",{"",""}, INDEX(SPLIT(TRANSPOSE(SPLIT(ini,"")),""))) ; split(s,"")},"select Col1, sum(Col2) group by Col1 label sum(Col2) ''"),LAMBDA(a,join("",a)&"")),,2^99)," ",""))))
The output is:
a500
a500b100
a390b100
a1390b100
a590b100
a590b300
a200b300
a200b250
a200b100
It is a string representation of the balance
Next step is to count the balanse for adjacent item:
=LAMBDA(items,operations,quantities,buy_key,BYROW({items,SCAN("",lambda(item,op,s,mbuy, INDEX(if(item="",,SUBSTITUTE(item," ","_")&""& IF(op=mbuy,1,-1)*s)))(items,operations,quantities,buy_key),LAMBDA(ini,s,if(s="",ini,
SUBSTITUTE(QUERY(BYROW(QUERY({if(ini="",{"",""}, INDEX(SPLIT(TRANSPOSE(SPLIT(ini,"")),""))) ; split(s,"")},"select Col1, sum(Col2) group by Col1 label sum(Col2) ''"),LAMBDA(a,join("",a)&"")),,2^99)," ",""))))},LAMBDA(r,if(index(r,1)="",,VLOOKUP(substitute(index(r,1)," ","_") , if(index(r,2)="",{"",""}, INDEX(SPLIT(TRANSPOSE(SPLIT(index(r,2),"")),""))) , 2, )))))(A4:A12,B4:B12,C4:C12,"buy")
The parameters are in the end. They are:
items,
operations,
quantities,
buy_key
buy_key is text "buy" in our case.
You can also build a Named Funtion:
The formula will show tips like this:
Please think of it as a great opportunity and I'm sure we'll be also able to calculate other comlicated things like FIFO/LIFO prices for each item.