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

Answers

  • Umesh Shah
    Umesh Shah ✭✭✭✭
  • Umesh Shah
    Umesh Shah ✭✭✭✭
    edited 12/03/20

    @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 |

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!