• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation of Average Selling Price of a particular stock?

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

  • Working File.xlsx
    12.4 KB · Views: 4
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

  • Working File.xlsx
    28.1 KB · Views: 6
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.
 
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

  • Copy of Working File.xlsx
    13.3 KB · Views: 2
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
IFERROR(-SUMPRODUCT(--(History!$B$3:$B$32=Positions!$A3)*(History!$C$3:$C$32="Buy")*History!$K$3:$K$32)/SUMPRODUCT((History!$B$3:$B$32=Positions!$A3)*(History!$C$3:$C$32="Buy")*History!$D$3:$D$32),"")

If any errors please rectify it ok
 
Back
Top