Please help with formula for averages
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
Answers
-
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! 🙂
-
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:
-
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;
-
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.
-
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"
-
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.
-
Thanks, it worked when I entered "Paula Shaver" but not when I entered Assigned@row
Thanks so much for your assistance
-
No problem, happy to have helped.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!