How to calculate weighted average inside an ArrayFormula based on multiple row-criteria?

Question

I'm trying to solve a problem for so long and I haven't figured out how to do this.

Basically, I need to calculate a weighted average based on multiple criteria. This criteria is going to be retrieved from a ArrayFormula (It means that I have columns with different values for the criterias, and I want to automatically fill the average based on that criteria using ArrayFormula).

This is my structure on the "main" table: Main Table Structure

And that is the table that I'm "searching" on: Search Table Structure

The "search" table works like a balance with all the operations made by the users. It contains all purchases and sells, with their correspondent prices and quantities.

Basically, I need the purchased price weighted average by user and ticker, where the prices are the values and the quantities are the weights. Using the above images as an example: "Calculate the weighted average purchased price for ticker ABEV3 that belongs to [email protected]". But that has to iterate to every row, using ArrayFormula.

Before implementing ArrayFormula, I was using queries successfully, but I found out that QUERY and ArrayFormula are incompatible when using a "Array" on the WHERE clause. I tried to use SUMPRODUCT as well, pairing it with FILTER, but it returns a size mismatch error.

Important to mention: On the "main" table, it's possible to have the same ticker for different users, but not the same ticker on the same user twice. It works like a composite primary key, as in databases.

Thanks in advance!

A copy of my sheet is available here.

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B; QUERY(FILTER({
 'Transactions - Variable Income'!A2:A&'Transactions - Variable Income'!B2:B\ 
 'Transactions - Variable Income'!D2:D*'Transactions - Variable Income'!E2:E}; 
 'Transactions - Variable Income'!C2:C="buy"); 
 "select Col1,avg(Col2) group by Col1"); 2; 0)))

(https://www.wallstreetmojo.com/average-vs-weighted-average/)

Answer 2

So, I managed to do an workaround.

I created another sheet to hold all sums and grouped queries.

On that sheet, I query the transactions sheets twice, one for buy operations and another for sell operations, grouping them by user and ticker. The result is something like this.

After that, the rows on that sheet automatically update once a transaction is added, and it's just a matter of doing a VLOOKUP on that query interval.

Why it was good for me? Because I need the totals for other purposes, and this satisfies both problems.