avg a column of days if they equal a value in another column

lisalettieri

I am trying to average time to sending an email to make an appointment after an inquiry for each team member. This is the formula I tried:

=AVERAGEIF({Case Consultation Tracker Range 4}, {Case Consultation Tracker Lead}, @cell = "SB")

The column to average is Range 4 which is a formula measuring the difference from Request to Email for Appt. Is it possible Smartsheet AVERAGE cannot do it for a formula column?

James Keuning

I think you need to show us the data in each of your tables.

Because what is the range Case Consultation Tracker Lead? And what is @cell = "SB"? Is SB a person and you want to average that person's response time? This formula expects a range in that third position.

I created a column called Rep Initials and put this formula in it:

=AVG(COLLECT({Case Consultation Tracker Range 4}, {Case Consultation Tracker Lead}, [Rep Initials]@row))

Basically, collect the data you need, and then apply the average to it.

Jgorsich

When you start typing the formula into Smartsheet, it will pop up a small text showing you how the formula is layed out. You've got yours setup backwards. The order is "range you are evaluating","criteria","range you are averaging".

Also, note that your criteria needs to be a specific logic test (with an "=" implied if it is just a value) - I'm presuming here that your team member is "SB", is that correct? Either way, you'd be better off thinking of it this way:

You've got some data collection sheet where "range 4" is the column that has the time it takes to make the appointment and "lead" is the column with your team member's names. In another sheet (or in the same sheet but in another column) you've got a list of all the team member's names with no duplicates, call this "list". If you are doing all of this in the same sheet then, it would look like this:

=averageif([lead]:[lead],list@row,[range 4]:[range 4])

Here, we are saying "Check the "lead" column and find all the rows where it matches with the name in this row of "list", then on those rows average the values in "range 4".

lisalettieri

So if the IF is {Case Consultation Tracker Lead}, @cell = "SB") then is =AVERAGEIF({Case Consultation Tracker Range 4}?

=AVERAGE(COLLECT({Case Consultation Tracker Lead}, @cell = "Steven Batutis", {Case Consultation Tracker Range 4}))

=AVERAGE(COLLECT({Case Consultation Tracker Lead}, @cell = "SB", {Case Consultation Tracker Range 4})

Meaning if Lead is SB, then average SBs.

Gets me unparseable error.

lisalettieri

data is in another sheet, Consultation Tracker, and I made a separate column to calculate the days bet request and response. Turned it into a column formula.

Now for each consultant I'm trying to average these days in my METRICS WORKSHEET from which I do the DASHBOARD.

Data looks like this

I'm only in my 3rd week for dashboarding - make me look good please!

lisalettieri

jgorsich: See data above, then formulas on separate sheet.

Your format yields: =AVERAGEIF(Case Consultation Tracker Lead}, = "Steven Batutis", {Case Consultation Tracker Range 200}:{Case Consultation Tracker Range 200})

Gets me UNPARSEABLE still. :(