Weighted average when item inventory reaches zero (Excel)

I have an excel worksheet containing transaction data (example image) and ran into a problem with weighted averages. The problem occurs after an item is fully sold/unloaded when it is bought again, the calculation includes the old price information of the items that were previously sold. I would probably need to use a function that "restarts" calculating the weighted average after the position reached zero for a specific item and is bought again at a new price. I do not know how to do this or if this is possible in Excel at all. I would really appreciate any ideas or help!

Formulas currently used are:

Col H =SUMIFS($F$2:F2; $D$2:D2; D2)

Col I =IF(B2="Buy";SUMPRODUCT(--($C$2:C2=C2); --($B$2:B2="Buy");$E$2:E2;$F$2:F2)/SUMIFS($F$2:F2;$C$2:C2;C2;$B$2:B2;B2);" ")

If you are able to use xmatch, you can search back for a previous occurrence of a zero and only include transactions after that:

=IF(H2=0,"-",
IFERROR(LET(prevZero,XMATCH(1,(H1:H$1=0)*(C1:C$1=C2),0,-1)+1,
SUMIFS(INDEX(G:G,prevZero):G2,INDEX(C:C,prevZero):C2,C2)/SUMIFS(INDEX(H:H,prevZero):H2,INDEX(C:C,prevZero):C2,C2)),
SUMIFS(G$2:G2,C$2:C2,C2)/H2))

assuming all items are sold in one transaction as in your sample data.

Another way of looking it which agrees with your idea is that for buy transactions:

New average = (Previous average * previous number of items on hand + Current price * number of items purchased) / Current number of items on hand

while for sell transactions the number of items on hand is updated but the average price is just carried forward.

This actually gives a simpler formula:

=IF(H2=0,0,
IFERROR(LET(prevTrans,XMATCH(TRUE,C1:C$1=C2,0,-1),
IF(B2="Sell",INDEX(J:J,prevTrans),(INDEX(H:H,prevTrans)*INDEX(J:J,prevTrans)+G2)/H2)),
E2))

I have added a new item C which ends up with an average price of $15 for the 100 remaining units as required.