Get Unique list from another sheet and calculate Average Time
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.
Best Answer
-
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.
Answers
-
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.
-
Thank you !!
-
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 |
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!