Get Unique list from another sheet and calculate Average Time

Umesh Shah

I have the following data in my sheet:

These requests are submitted via a form into the sheet.

How do i get a unique list of NAMES and then average their response rate?

So What i want to see is one entry for every NAME on the list and next to it their average time across all their entries.

L_123

So you need 2 columns.

column1: Names

First Cell:

=index(distinct(name:name),1

Rest of cells:

=index(distinct(name:name),1+count(names$1:names1))

Then drag down.

Column2: Avg

=averageif(name:name,names@row,[time to complete]:[time to complete])

Then drag down.

You can slap an iferror( ,"") on both of those if you have an expanding number of names to dynamically generate new values below when new names are added if you want.

L_123

So you need 2 columns.

column1: Names

First Cell:

=index(distinct(name:name),1

Rest of cells:

=index(distinct(name:name),1+count(names$1:names1))

Then drag down.

Column2: Avg

=averageif(name:name,names@row,[time to complete]:[time to complete])

Then drag down.

You can slap an iferror( ,"") on both of those if you have an expanding number of names to dynamically generate new values below when new names are added if you want.

Umesh Shah

Thank you !!

Umesh Shah

@L@123

i have a follow-up question:

how do i put these formulas into another blank sheet while the formula references my source sheet?

the first part of the formula works, but the second part doesn't where the the rows increment by 1 as i copy them down.

=index(distinct(name:name),1+count(names$1:names1))

the distinct(name:name) is able to reference a different sheet, but how do i do that for the latter part?

count(names$1:names1)

where you have a static reference | names$1 | and a reference which increments as i copy the formula down to other rows | names1 |