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

 

[bullit_nl]

Is it possbile to add a wildcard to the search like: "=*bal"? To sum and average all bals?

I gues i need the dynamic part to be able to use it in the pivot table, right?

 

[Aladin Akyurek]

Looks like

=SUMPRODUCT(--($A$2:$A$100="red bal"),$F$2:$F$100,$H$2:$H$100)/SUMIF($A$2:$A$100,"red bal",$F$2:$F$100)

Right?

Yes! This is right...it gives me the average

Is it possbile to ad a wildcard to the search like: "=*bal"? To sum and average all bals?

I gues i need the dynamic part to be able to use it in the pivot table, right?

Activate Formulas | Name Manager.
Activate the Ne tab.
Enter Lrow as Name.
Enter the following in the Refers to box:

=MATCH(9.99999999999999E+307,Sheet1!$F:$F)

Click OK.

Note. On non-American systems, use comma for the decimal dot and semi-colon for the comma as list separator. Adjust for the sheet name.

Define Product as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

Define Qty as referring to:

=Sheet1!$F$2:INDEX(Sheet1!$F:$F,Lrow)

Define Price as referring to:

=Sheet1!$H$2:INDEX(Sheet1!$H:$H,Lrow)

Now we can make our weighted average to refer dynamic ranges...

=SUMPRODUCT(--ISNUMBER(SEARCH("bal",Product)),Qty,Price)/
  SUMIF(Product,"*bal",Qty)

 

[bullit_nl]

Nice!.......The weighted average with the dynamic ranges are working but not yet with the right result.


This part gives me the total amount of all balls:
=SUMPRODUCT(--ISNUMBER(SEARCH("bal",Product)),Qty,Price)

This part gives me only the quantity of products starting with bal and not the rest:
=SUMIF(Product,"*bal",Qty)

I tried playing with the wildcard in both parts but couldn't work it out.

Further i'm wondering how to implement this formula for all (or a part of) +/- 38 products in combination with a pivot table because i never know on witch and how many rows my products are.

 

[Aladin Akyurek]

Nice!.......The weighted average with the dynamic ranges are working but not yet with the right result.


This part gives me the total amount of all balls:
=SUMPRODUCT(--ISNUMBER(SEARCH("bal",Product)),Qty,Price)

This part gives me only the quantity of products starting with bal and not the rest:
=SUMIF(Product,"*bal",Qty)

I tried playing with the wildcard in both parts but couldn't work it out.

Further i'm wondering how to implement this formula for all (or a part of) +/- 38 products in combination with a pivot table because i never know on witch and how many rows my products are.

Try...

=SUMPRODUCT(--ISNUMBER(SEARCH("bal",Product)),Qty,Price)/
  SUMIF(Product,"*bal*",Qty)

I did not get the problem with the products... You can either create a list of products and reference the entries of that list in the SumProduct formula or switch completely to PT where you use a calculated field as Andrew suggested in post #10.

 

[bullit_nl]

The changement worked, thanks.

The thing i dont' understand in the code is why the wildcard only has influence on the SUMIF part. What do i have to change on the code SUMPRODUCT if i want to play with exact or not exact finds?

Can you help me with changing the sumproduct formula creating a list of products

I dont know how i can create a list of products and reference the entries of that list in the sumproduct formula. Can you help me doing that?

 

[Aladin Akyurek]

The changement worked, thanks.

The thing i dont' understand in the code is why the wildcard only has influence on the SUMIF part. What do i have to change on the code SUMPRODUCT if i want to play with exact or not exact finds?

=SUMPRODUCT(--ISNUMBER(SEARCH("bal",Product)),Qty,Price)/
  SUMIF(Product,"*bal*",Qty)

effects a partial match regarding products, while

=SUMPRODUCT(--(Product="moerbei"),Qty,Price)/
  SUMIF(Product,"moerbei",Qty)

would carry out an exact match.

Can you help me with changing the sumproduct formula creating a list of products

I dont know how i can create a list of products and reference the entries of that list in the sumproduct formula. Can you help me doing that?

Which sheet are you entering the first instance of the SUMPRODUCT formula and in which cell?

 

[bullit_nl]

Sheet name: Data
First cell: S2

 

[Aladin Akyurek]

Sheet name: Data
First cell: S2

Define Ivec as referring to:

=ROW(Product)-ROW(INDEX(Product,1,1))-1

R2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Product,SMALL(IF(FREQUENCY(IF(Product<>"",
  MATCH(Product,Product,0)),Ivec),Ivec),ROWS($R$2:R2))),"")

S2, just enter and copy down:

=SUMPRODUCT(--(Product=$R2),Qty,Price)/
  SUMIF(Product,$R2,Qty)

 

[bullit_nl]

Thank you for the codes. It works.

Is it possible to implement it in a different way so that i will see only the average price in column S from the coresponding product in A?

Example:
A2: red ball >>> S2: the average price of all red balls
A3: green fish >>> S3: the average price of all green fishes
A4: brown Sugar >>> S4: the average price of all brown sugars
A5: red ball >>> S5: the average price of all red balls
A6: green fish >>> S6: the average price of all green fishes
A7: brown Sugar >>> S7: the average price of all brown sugars
etc

 

[Aladin Akyurek]

Thank you for the codes. It works.

Is it possible to implement it in a different way so that i will see only the average price in column S from the coresponding product in A?

Example:
A2: red ball >>> S2: the average price of all red balls
A3: green fish >>> S3: the average price of all green fishes
A4: brown Sugar >>> S4: the average price of all brown sugars
A5: red ball >>> S5: the average price of all red balls
A6: green fish >>> S6: the average price of all green fishes
A7: brown Sugar >>> S7: the average price of all brown sugars
etc

If don't think I understand the request.

Post #28 creates a unique list of products from Product in column R and calculates a weighted average in column S for each product in S.

Are you then asking to drop R and use in S the products in A directly?