Summarising/averaging data with array formula

Gareth246

New Member
Joined
Aug 20, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
Does it have to be 'live'? If not, Copy the whole column, paste in C, sort, delete numbers, delete duplicates, then in D
Rich (BB code):
=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

New Member
Joined
Aug 20, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Does it have to be 'live'? If not, Copy the whole column, paste in C, sort, delete numbers, delete duplicates, then in D
Rich (BB code):
=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

New Member
Joined
Aug 20, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Does it have to be 'live'? If not, Copy the whole column, paste in C, sort, delete numbers, delete duplicates, then in D
Rich (BB code):
=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

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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.
Power Query:
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top