Summarising/averaging data with array formula

[Gareth246]

In a table I have:

cell A1 is the persons name
Cell A2 in an amount of time such as 5.00

there are people but over 100 entry’s. I.e. each person has multiple entry’s.

what formula could I use to produce a summary let’s say one of the persons Name was Dave (in column A)

In column C I want it to show me a list of the names (5 people) and next to their name their AVERAGE time in column D

i.e. Dave 4.35

here’s the catch. If I add another name and time to the original list I want a summary of that person to automatically appear in column c and d

is this possible? Would it be an array formula?

 

[C Moore]

Does it have to be 'live'? If not, Copy the whole column, paste in C, sort, delete numbers, delete duplicates, then in D

=AVERAGEIF(A2:A9,C2,A3:A10)

C2 is the unique version of Dave you pasted, then the criteria range and sum range are offset by 1 cell (notice A2:A9->A3:A10).

 

[Gareth246]

Does it have to be 'live'? If not, Copy the whole column, paste in C, sort, delete numbers, delete duplicates, then in D

=AVERAGEIF(A2:A9,C2,A3:A10)

C2 is the unique version of Dave you pasted, then the criteria range and sum range are offset by 1 cell (notice A2:A9->A3:A10).

It has to be live, for more context:
I’m using power query to merge 10 worksheets. Each person has their own worksheet I refresh the sheet every 10 minutes

if in that time someone, or a new person has, “logged” there name and a time I need to be able to see it in a summary

 

[Gareth246]

Does it have to be 'live'? If not, Copy the whole column, paste in C, sort, delete numbers, delete duplicates, then in D

=AVERAGEIF(A2:A9,C2,A3:A10)

C2 is the unique version of Dave you pasted, then the criteria range and sum range are offset by 1 cell (notice A2:A9->A3:A10).

Do you think an advanced filter macro that auto runs every 5 minutes or so accompanied by an average if function would do the trick?

 

[C Moore]

Power Query Convert Repeating Rows to Columns will show you how to move them to side by side form. Then you can group by with name as the grouping and average as the aggregate.

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each _[Column1], type table}}, GroupKind.Local),
    Custom1 = Table.FromRows(#"Grouped Rows"[All]),
    #"Grouped Rows1" = Table.Group(Custom1, {"Column1"}, {{"Average Time", each List.Average([Column2]), type number}})
in
    #"Grouped Rows1"