Hi,
I want to use the sumproduct formula to calculate weighted averages in my pivot table.
Prices are in column B and quantities are in column M.
The problem is that the range of columns B and M are changing every time i refresh the data form the pivot table and the total at the bottom of my pivot table witch i need to see.
How can i use the sumproduct formula without having to edit it all the time and not include the total? I know that it's possible to use a dynamic range but don't know how to use it.
Formula:
=SUMPRODUCT(M6:M14;B6:B14)/SUM(M6:M14)
Thanks
I want to use the sumproduct formula to calculate weighted averages in my pivot table.
Prices are in column B and quantities are in column M.
The problem is that the range of columns B and M are changing every time i refresh the data form the pivot table and the total at the bottom of my pivot table witch i need to see.
How can i use the sumproduct formula without having to edit it all the time and not include the total? I know that it's possible to use a dynamic range but don't know how to use it.
Formula:
=SUMPRODUCT(M6:M14;B6:B14)/SUM(M6:M14)
Thanks