Weighted average selling price base on 3 conditions

[Salamullah]

Hi all,

Please help me with formula which calculate weighted average selling prices (WAP) based on volume


Sheet 2 result

Product No. WAP 22-400 17.05 22-500 27.22 22-100 20.00 <colgroup><col><col></colgroup><tbody> </tbody> <strike></strike>

<colgroup><col><col></colgroup><tbody>
</tbody>

Sheet 1 data

	Product USP Volume % WA WAP 22-100 20.00 14,000 100 20.00 20.00 22-400 15.00 50,000 64% 9.62 22-400 10.00 13,000 17% 1.67 22-400 30.00 15,000 19% 5.77 17.05 22-500 30.00 50,000 63% 18.99 22-500 40.00 12,000 15% 6.08 22-500 10.00 17,000 22% 2.15 27.22 <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody> <strike></strike>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Salamullah]

Formula need in sheet 2 column B, I have list of products with different selling prices and volumes

 

[sandy666]

Does it have to be a formula?

With PowerQuery aka Get&Transform:

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([WAP] <> null)),
    Group = Table.Group(Filter, {"Product"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "WAP", each List.Distinct(Table.Column([Count],"WAP"))),
    Expand = Table.ExpandListColumn(List, "WAP")
in
    Expand[/SIZE]

Product	USP	Volume	%	WA	WAP		Product	WAP
22-100	20​	14,000​	100​	20​	20​		22-100	20​
22-400	15​	50,000​	64%​	9.62​			22-400	17.05​
22-400	10​	13,000​	17%​	1.67​			22-500	27.22​
22-400	30​	15,000​	19%​	5.77​	17.05​
22-500	30​	50,000​	63%​	18.99​
22-500	40​	12,000​	15%​	6.08​
22-500	10​	17,000​	22%​	2.15​	27.22​

You can put green table wherever you want

 

[Marcelo Branco]

Try

Formula in Sheet2 B2 copied down
=SUMPRODUCT(--(Sheet1!A$2:A$8=A2),Sheet1!B$2:B$8*Sheet1!C$2:C$8)/SUMIF(Sheet1!A$2:A$8,A2,Sheet1!C$2:C$8)

M.

 

[Imran Laldin]

use this for your required result.


=IF(A2=A3,"",SUMPRODUCT(--(Sheet1!A$2:A$8=A2),Sheet1!B$2:B$8*Sheet1!C$2:C$8)/SUMIF(Sheet1!A$2:A$8,A2,Sheet1!C$2:C$8))

 

[Salamullah]

Many thanks all for quick answers, will apply and confirm,

 

[Salamullah]

How can I take average of WA Column E in Sheet 2 showing Products ?