Calculate average of a range with multiple column

Question

I have a scenarios, where I have to calculate average price of shares from a set of date. Consider I have following data.

Now I want to represent the data in following format:

Above table will store the average price whenever a new scrip is added in the first table. I have tried AVERAGEIFS(), but it calculate averages only for a single column range. But I have to calculate average price using price * quantity across the range for the given scrip. Please suggest.

Answer 1

Not sure I understand the question. If you're trying to get the total amount base on the average price without a helper column you could use this

=AVERAGEIF($B$3:$E$8,B12,$E$3:$E$8)*SUMIF($B$3:$E$8,B12,$C$3:$C$8)

Answer 2

You can use Power Query (available in Excel 2010+) for this.

In Excel 2016+ (may be different in earlier versions):

• select some cell within the data table
• Data / Get & Transform / From Table/Range
• In the UI, open the Advanced Editor
  • Paste the M-Code below into the window that opens
  • Change the Table Name in Line 2 to reflect the actual table name in your worksheet.

NOTE: In the UI, in the Applied Steps window, float your cursor over the information icons to read the comments for explanations. Also you can double click on the gear icons for more information as to how those steps were set up

M Code

let
    //Change Table name to correct name
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stocks", type text}, {"Quantity", Int64.Type}, {"Date", type date}, {"Price", type number}}),
    
    //Group by Stock
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Stocks"}, {{"Grouped", each _, type table [Stocks=nullable text, Quantity=nullable number, Date=nullable date, Price=nullable number]}}),

    //Sum quantity for each stock
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Quantity", each List.Sum(Table.Column([Grouped],"Quantity"))),

    //Compute weighted average price for each group of stocks
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Price", each List.Accumulate(
List.Positions(Table.Column([Grouped],"Quantity")),
    0,
    (state, current) =>state + Table.Column([Grouped],"Price"){current} * 
    Table.Column([Grouped],"Quantity"){current})    
        / List.Sum(Table.Column([Grouped],"Quantity"))),

    //Compute Total Amount for each stock
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Amount", each [Quantity]*[Price]),

    //Remove extraneous Columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Grouped"})
in
    #"Removed Columns"

Answer 3

Are you allowed to add a column to your data for calculating the total_price? For example, column E = Quantity * Price.

Then your calculations table would be quite simple. Formulas for row 3:

    Quantity: =SUMIFS(B:B,A:A,G3)
    Average_Price: =SUMIFS(E:E,A:A,G3) / SUMIFS(B:B,A:A,G3)
    Amount: =H3*I3