Need help with formulas calculating cost basis on shares

ashko20 

It's not clear to me (as somebody who also tracks stock and options purchases and sales) what your other formulas are doing here, before we even get to that final column. And, while we're at it, headings like "Net Proceeds" are misleading in a minor way since that tends to imply proceeds after a sale; I'd go with plain ol' "Net" to cover both outflow and inflow of funds.

Continuing, though, with points where clarification is needed:  in your first row, why are you showing $650 for a transaction where the quantity is 100 and the price 7? Why not $700? Is there a $50 fee or commission hidden in there?

Now, to get to the calculation you asked about: it would be helpful if you could post a copy of this actual spreadsheet on OneDrive or GoogleDrive with a link pasted here. Your posted data here leaves off the Column and Row headers so we can't really determine which set of cells is referenced by (for example) $CL2, which is central to the IF condition in your formula. Is that the first "SYMBOL" column, or the "Identifier (helper)" column?

All of that having been said, if I were doing this, I'd keep the transaction records as one database, and place the calculation of weighted average cost in a separate sheet (call it a "Dashboard" sheet), where it wouldn't vary row by row but would simply aggregate all of the data pertinent to HUT or PLTR into a display with one row per stock. In the attached I've put that summary data below the data table, but that's not where I'd keep it in the real workbook. Good design generally separates a transactional database from whatever summaries you're wanting to do.

The formula that calculates weighted cost is this:

=ABS(SUM(FILTER($E$5:$E$11,$A$5:$A$11=A17)))/SUM(FILTER($B$5:$B$11,$A$5:$A$11=A17))

Essentially all it's doing is adding up the numbers in column E ("Net Proceeds") for the stock in question and dividing that by the sum of the shares purchased (column B) and showing the absolute value of that result.

Assuming your larger real database is more complex, showing sales as well as purchases, multiple stocks, the formula would need to be tweaked a bit to account for the possibility of sales, etc., but the basic idea would still work.

The formulas here are very dependent on the FILTER function, which requires Excel 2021 or newer (a Microsoft 365 subscription)