CODEWITHSUNDEEP
google-sheetsmathematical-optimizationgoogle-sheets-formulagoogle-sheets-query
FabianoLothor
FabianoLothor

Reputation: 2985

Price payed for the products column

I have been pass all day into this and I couldn't find any solution to solve my problem.

I have a sheet, something like: https://docs.google.com/spreadsheets/d/1M1yj_FC_dGOjRxQv-Z-toE-niL_o3XkGEch6mGlebNg/edit?usp=sharing

I need one way to populate the BUY PRICE column, with the price paid for the product.

The operations are ordered by date (I use an ID to simplify).

Basically the D3 cell, needs be filled with $10 price. Because the store has 3 in stock.

D9 cell needs to be filled with $20 price because in the first purchase of the B PRODUCT I pay $ 15 per one unity, and sometime later, I buy more two per $25 (line six).

If I was seller just one product B, the unit price has been $15, but, I sell two B products on line 8, so, I need to calculate the average of my unity values ables in my stock.

I tried to resolve it using the FILTER function but doesn't work.

=IF(
        $C2="SELL";
                FILTER(
                        $F$2:$F;
                        $A$2:$A<=$A2;
                        $B$2:$B=$B2
                );
        "-"
)

It is like an assigned problem. but I couldn't find a way to solve it.

Upvotes: 0

Views: 39

Answers (1)

player0
player0

Reputation: 1

=IF(C2="SELL", AVERAGE(QUERY({INDIRECT("A1:C"&ROW()),INDIRECT("E1:G"&ROW())}, 
 "select Col5 where Col2='"&B2&"' and Col3='BUY' limit "&E2&"offset "&
 SUMIFS(INDIRECT("E1:E"&ROW()-1), 
 INDIRECT("B1:B"&ROW()-1), B2, INDIRECT("C1:C"&ROW()-1), "=SELL"), 0)), "-")

0

Upvotes: 1

Related Questions