Jun 01 2021 05:00 PM
Hi everybody
I would really appreciated your help. I can't get the formula right :(
Basically I want to know the average buy and sell price for BTC (and then various other tickers) from all the trades. I attach a spreadsheet.
Thank you so much for your help!
Jun 01 2021 09:24 PM
@nataleee1460 Perhaps you'll find a pivot table more suitable to this kind of summary. Please see attached.
Jun 01 2021 09:49 PM
SolutionIn SUMPRODUCT, you cannot filter using AND the way you did. Instead, you have to include the "filter" inside the function. So, to get the weighted average, use
=SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30,D2:D30)/SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30)
The double negation converts the boolean result into numbers (1 for TRUE and 0 for FALSE).
Jun 02 2021 12:52 PM
Jun 02 2021 12:54 PM
Jun 01 2021 09:49 PM
SolutionIn SUMPRODUCT, you cannot filter using AND the way you did. Instead, you have to include the "filter" inside the function. So, to get the weighted average, use
=SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30,D2:D30)/SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30)
The double negation converts the boolean result into numbers (1 for TRUE and 0 for FALSE).