# Pivot table get weighted average with sumproduct

#### bullit_nl

##### Active Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Can't you use the source data directly?

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

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?

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.

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.

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?

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?

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.

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

Replies
0
Views
196
Replies
0
Views
279
Replies
2
Views
455
Replies
1
Views
408
Replies
2
Views
597

1,219,569
Messages
6,149,039
Members
450,852
Latest member
dmotz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back