Please help with formula for averages

Paula Shaver

I am trying to create an average using data in Multiple Colums columns in another sheet. I am not finding luck with the formulas I have tried so far using AVG(Collect) or Average and I've tried adding AND or OR statements. I know I am just entering them incorrectly but am unsure how to fix them.

What I am trying to do is get the average number of days tickets have been open for, for a specific assigned technician for tickets if = Completed OR Withdrawn. Eg Average # of days

Data is in multiple columns in another sheet titled ITS Consulting Formulas

Reference Data is

Assigned (Column title) = Paula Shaver

Status (Column title) = Completed OR Withdrawn

Time Ticket has been open (days) (Column Title) - This is a column of data that already had calculations indicating the # of business days each ticket has been open for regardless of their status.

Can somebody please help me. It would be greatly appreciated.

Thank you, Paula

Nick Korna

Hi @Paula Shaver,

Something along the lines of this?

=IFERROR(AVG(COLLECT({Ticket Open For}, {Assigned}, User@row, {Status}, OR(@cell = "Completed", @cell = "Withdrawn"))), "")

Example data:

Cross sheet with averages:

Hope this helps, but if you've any problems/questions then just post them! 🙂

Paula Shaver

Thanks Nick, when I am trying that I get the message back #UNPARSEABLE

This is what I've entered but as you can see I am getting the error user@row

=IFERROR(AVG(COLLECT({Total Days All Tickets Open}, {Assigned}, User@row, {Status}, OR(@cell = "Completed", @cell = "Withdrawn"))), "")

Not sure what you mean by the section Cross sheet with averages:

Paula Shaver

I have a sheet called ITS Consulting Formulas and a Column titled Paula Shaver - Days Tickets Completed for

In the cell directly under the title is where I entered the formula

=IFERROR(AVG(COLLECT({Total Days All Tickets Open}, {Assigned}, User@row, {Status}, OR(@cell = "Completed", @cell = "Withdrawn"))), "")

The other sheet titled Consulting Services CSM Requests is where the data is that I am pulling from as such;

Nick Korna

You'll need to define the cross sheet references - the items in { } brackets as this can't be done with simply typing them in. In this case they should refer to the relevant columns (Smartsheet defaults them to the sheet name & a number).

For example, picking the data sheet (on the left, you can narrow the search by typing in the box in the top screenshot) and then clicking the column header and changing the name (just to make it easier to know what it is referring to):

Hope this makes the cross sheet references a bit clearer - if you're still getting any errors after changing the references, let us know.

Paula Shaver

Ah yes, thank you Nick. I did have all the references in. I am getting a red wiggly line under the section User@row, in the formula. It doesn't know what this means.

Instead of the above, is there a way just to also say if Assigned = "Paula Shaver"

Nick Korna

You can, just change the User@row part to "Paula Shaver" (name enclosed in " "). However, you'll then need to update it for each name on the sheet/list.

I am guessing your sheet does not have a "User" column along the lines of mine, so you can use whatever column header is there instead - for example it will be perfectly happy if you substitute Assigned@row instead.

Paula Shaver

Thanks, it worked when I entered "Paula Shaver" but not when I entered Assigned@row

Thanks so much for your assistance

Nick Korna

No problem, happy to have helped.