# Calculation of Average Selling Price of a particular stock?

#### SONJOE JOSEPH

##### Member
Good Noon to all Excel Experts. I was just facing a problem to do a calculation to find out the Average Selling Price of a stock. I have attached a working file were there are two sheets named "His" and "Positions". Just click on the "Positions" sheet and the first stock u will find is "Heidelberg" . I need to find out the Average Selling Price using the Contact Value which is given in the "His" Sheet were it should only take all the SELL done of a particular stock and divide the Contract Value by the Quantity.

In the case of Heidelberg SELL transaction happening on

Date Contract Value Qty
28 Aug 20 1957.89 -10
22 Sep 20 1791.91 -10

So the calculation of Avg Selling Price is total of (1957.89+1791.91) / 20 = 187.54. This answer should come in the "Positions" sheet at B3 straight to Heidelberg. Like this i want the results of others too.

Hope some of u will help me in solving it and attaching the working file.

Thanks & Regards,

#### Attachments

• 12.4 KB Views: 3

#### AlanSidman

##### Well-Known Member
Calculated using Power Query

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Action] = "Sell")),
#"Inserted Division" = Table.AddColumn(#"Filtered Rows", "Division", each [Contract Value] / [Qty], type number),
#"Grouped Rows" = Table.Group(#"Inserted Division", {"Company Name"}, {{"AVG Sell", each List.Average([Division]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Company Name", Order.Ascending}})
in
#"Sorted Rows"``````

#### Attachments

• 28.1 KB Views: 4

#### SONJOE JOSEPH

##### Member
Calculated using Power Query

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Action] = "Sell")),
#"Inserted Division" = Table.AddColumn(#"Filtered Rows", "Division", each [Contract Value] / [Qty], type number),
#"Grouped Rows" = Table.Group(#"Inserted Division", {"Company Name"}, {{"AVG Sell", each List.Average([Division]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Company Name", Order.Ascending}})
in
#"Sorted Rows"``````
Without giving a query is it possible to do with a formula. like some if match function and then using the average formula. I search lot of youtube videos and tried but nothing was working.

#### pecoflyer

##### Active Member
See attached for a possible solution with formulas
=IFERROR(-SUMPRODUCT(--(His!\$B\$3:\$B\$32=Positions!\$A3)*(His!\$C\$3:\$C\$32="sell")*His!\$K\$3:\$K\$32)/SUMPRODUCT((His!\$B\$3:\$B\$32=Positions!\$A3)*(His!\$C\$3:\$C\$32="sell")*His!\$D\$3:\$D\$32),"")

#### Attachments

• 13.3 KB Views: 1

#### SONJOE JOSEPH

##### Member
See attached for a possible solution with formulas
=IFERROR(-SUMPRODUCT(--(His!\$B\$3:\$B\$32=Positions!\$A3)*(His!\$C\$3:\$C\$32="sell")*His!\$K\$3:\$K\$32)/SUMPRODUCT((His!\$B\$3:\$B\$32=Positions!\$A3)*(His!\$C\$3:\$C\$32="sell")*His!\$D\$3:\$D\$32),"")

Thank you friend its working fine...I edited the same formula for "BUY" but its not giving the correct resutls. Just see the forumala