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

[TABLE="width: 144"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 144"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Product No.[/TD]
[TD]WAP[/TD]
[/TR]
[TR]
[TD]22-400[/TD]
[TD] 17.05 [/TD]
[/TR]
[TR]
[TD]22-500[/TD]
[TD] 27.22 [/TD]
[/TR]
[TR]
[TD]22-100[/TD]
[TD] 20.00 [/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 data


[TABLE="width: 328"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 429"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]USP[/TD]
[TD]Volume[/TD]
[TD]%[/TD]
[TD]WA[/TD]
[TD]WAP[/TD]
[/TR]
[TR]
[TD]22-100[/TD]
[TD] 20.00 [/TD]
[TD] 14,000 [/TD]
[TD="align: right"]100[/TD]
[TD] 20.00 [/TD]
[TD] 20.00 [/TD]
[/TR]
[TR]
[TD]22-400[/TD]
[TD] 15.00 [/TD]
[TD] 50,000 [/TD]
[TD="align: right"]64%[/TD]
[TD] 9.62 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22-400[/TD]
[TD] 10.00 [/TD]
[TD] 13,000 [/TD]
[TD="align: right"]17%[/TD]
[TD] 1.67 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22-400[/TD]
[TD] 30.00 [/TD]
[TD] 15,000 [/TD]
[TD="align: right"]19%[/TD]
[TD] 5.77 [/TD]
[TD] 17.05 [/TD]
[/TR]
[TR]
[TD]22-500[/TD]
[TD] 30.00 [/TD]
[TD] 50,000 [/TD]
[TD="align: right"]63%[/TD]
[TD] 18.99 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22-500[/TD]
[TD] 40.00 [/TD]
[TD] 12,000 [/TD]
[TD="align: right"]15%[/TD]
[TD] 6.08 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22-500[/TD]
[TD] 10.00 [/TD]
[TD] 17,000 [/TD]
[TD="align: right"]22%[/TD]
[TD] 2.15 [/TD]
[TD] 27.22 [/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Product[/td][td=bgcolor:#5B9BD5]USP[/td][td=bgcolor:#5B9BD5]Volume[/td][td=bgcolor:#5B9BD5]%[/td][td=bgcolor:#5B9BD5]WA[/td][td=bgcolor:#5B9BD5]WAP[/td][td][/td][td=bgcolor:#70AD47]Product[/td][td=bgcolor:#70AD47]WAP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]22-100[/td][td=bgcolor:#DDEBF7]

20​

[/td][td=bgcolor:#DDEBF7]

14,000​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td=bgcolor:#DDEBF7]

20​

[/td][td=bgcolor:#DDEBF7]

20​

[/td][td][/td][td=bgcolor:#E2EFDA]22-100[/td][td=bgcolor:#E2EFDA]

20​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]22-400[/td][td]

15​

[/td][td]

50,000​

[/td][td]

64%​

[/td][td]

9.62​

[/td][td][/td][td][/td][td]22-400[/td][td]

17.05​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]22-400[/td][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]

13,000​

[/td][td=bgcolor:#DDEBF7]

17%​

[/td][td=bgcolor:#DDEBF7]

1.67​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]22-500[/td][td=bgcolor:#E2EFDA]

27.22​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]22-400[/td][td]

30​

[/td][td]

15,000​

[/td][td]

19%​

[/td][td]

5.77​

[/td][td]

17.05​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]22-500[/td][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

50,000​

[/td][td=bgcolor:#DDEBF7]

63%​

[/td][td=bgcolor:#DDEBF7]

18.99​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]22-500[/td][td]

40​

[/td][td]

12,000​

[/td][td]

15%​

[/td][td]

6.08​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]22-500[/td][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]

17,000​

[/td][td=bgcolor:#DDEBF7]

22%​

[/td][td=bgcolor:#DDEBF7]

2.15​

[/td][td=bgcolor:#DDEBF7]

27.22​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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 ?