Pivot table - How to calculate a weighted average

Question

Level 1

34 points

Pivot table - How to calculate a weighted average

I'm trying to come up with an average price for each stock in a stock portfolio of 25 stocks, where each stock has a number of different buys over time, each time with different lot sizes and purchase prices

Creating a pivot table that shows a row for each stock, with total cost and total shares is easy. However I can't figure out how to divide the two to come up with an average price. I'm currently copy/pasting the pivot table data into another table, then adding a formula column. But this seems pretty inelegant.

Does anyone have any suggestions? Is there an elegant solution?

MacBook Pro 13″, macOS 11.1

Posted on Jan 30, 2023 6:33 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,550 points

Jan 30, 2023 9:25 PM in response to hab1

Interesting problem! Something like this should work.

I've added an Avg Cost column to the data table with the transactions whose formula \ calculates how much of the average cost of all transactions for that security to allocate to each line. It uses the proportion of the current quantity to the total quantity to perform that allocation. When summed in the Pivot Table along with the other columns, this will once again equal the average cost of all transactions for that security.

The formula I used in F2, filled down, is entered as:

=SUMIF(B,B2,E)/SUMIF(B,B2,C)*C2/SUMIF(B,B2,C)

If your region uses , as a decimal separator replace the , in the formula with ; giving this:

=SUMIF(B;B2;E)/SUMIF(B;B2;C)*C2/SUMIF(B;B2;C)

The "Check" tables at the lower right in my screenshot were used to check the results. They are not needed.

SG

Reply