Stock Balance Inventory ArrayFormula in Google Sheets

Question

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.

How about [FIFO/LIFO](https://en.wikipedia.org/wiki/FIFO_and_LIFO_accounting)? Sure it wolud be also possible with `LAMBDA` and friends. — Max Makhrov, Sep 15 '22 at 09:56
@osm FIFO/LIFO is possible with built-in functions, but the calculation becomes too heavy. Here's my app-script version: https://docs.google.com/spreadsheets/d/1rAvS6bhdp6xJUHmwCRzmLwzhmBourfa5XcLvyW4AsEA/edit#gid=1341352795 — Max Makhrov, Oct 20 '22 at 14:16
@Max-Markhrov good effort thanks for sharing, try replacing the foxes with `{a•a,110...}` to make the arrays easily readable --- if you can is it worth it to make a question asking how to baypass "calculation becomes too heavy"? --- - more info on lambda limitation in this [question](https://stackoverflow.com/q/73815258/19529694). — Osm, Oct 20 '22 at 15:26

Harun24hr · Answer 1 · 2022-09-15T10:57:37.573

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))))

Max Makhrov · Answer 2 · 2022-09-15T10:10:21.780

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:

Notes

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.

Stock Balance Inventory ArrayFormula in Google Sheets

2 Answers2

Notes