Pivot table get weighted average with sumproduct

[bullit_nl]

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

 

[Aladin Akyurek]

Can't you use the source data directly?

 

[bullit_nl]

No this isn't possible....but what does that change?

 

[Aladin Akyurek]

No this isn't possible....but what does that change?

Running calculations on the source data is more or less straightforward.

If you build pivot tables, you need to recruit the functionality they offer. Now you appear to apply weighted average to a pivot table area, considering it as data source.

That said, what is the current area of the target pivot table?

 

[bullit_nl]

Can't you use the source data directly?

Ok i have the source data now.

Column F = quantity
Column H = price
Column A = description
Column R = total price (F*R)

Example:
In colomn A i have 38 different descriptions with 1.500 entries. Some descriptions have 12 entries others 183, etc. This changes when the source data is renewed.

 

[Aladin Akyurek]

Ok i have the source data now.

Column F = quantity
Column H = price
Column A = description
Column R = total price (F*R)

Example:
In colomn A i have 38 different descriptions with 1.500 entries. Some descriptions have 12 entries others 183, etc. This changes when the source data is renewed.

I must have confused you...

If you want to run weighted average on the source data (my preference), you need to specify the ranges the weighted average must refer to.

If you want to run weighted average on the data that appears in the pivot table you have built, specify the current range this pivot table occupies. The idea is that the formula refereces the right pivot tables range whenever the pivot table refreshed.

If you go for the latter option, try to specify the range of the current pivot table.

 

[bullit_nl]

Now i'm a little confused.



I want to run weighted average on the source data (your preference)

What do I need to specify more then the ranges in my last post?

 

[Aladin Akyurek]

Now i'm a little confused.



I want to run weighted average on the source data (your preference)

What do I need to specify more then the ranges in my last post?

Ok. What is the SumProduct formula we need?

 

[bullit_nl]

I want to sumproduct based on the description name in column A.

So if for example a discription name in A is "red bal" or "green fish" then i want to sum the quantities in column F and the prices in column H or R.

 

[Andrew Poulsom]

Can't you just add a Calculated Field to your Pivot Table with the formula =total price/quantity?