Average formula for text field
Hi I’m new to smartsheet
I am trying to input a formula into the sheet summary field that will average “closed complete” and “closed cancelled” for a column labeled “status”
I was able to count the two fields for both but not average them with the proper formula
thank you
Best Answer
-
First you count the closed and then divide by the count of the total.
=COUNTIFS(Status:Status, "Closed Complete") / COUNT(Status:Status)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
I image the intended outcome is to have it read "Closed Complete" if there are more of those and "Closed Cancelled" if there are more of those, right? if so, you'll want a nested if() statement instead of an avg().
try something like
if(Count("Closed Complete")>Count("Closed Cancelled"),"Closed Complete", if(Count("Closed Cancelled")>Count("Closed Complete"),"Closed Cancelled","Something Else"))
E.G.:: if there are more "closed complete" than "closed cancelled," say "closed complete," otherwise; if there are more "closed cancelled" than "closed complete," say "closed cancelled," otherwise say 'something else."
-
Thank you Nik, I was hoping to get the % of those tickets that are close complete and % of closed cancelled out of the total column for Status because there will be times there will be an “open” status in future reports that I want to have to show % Of out of the total so I wanted to understand how to do this with the data on this report. I am going to try this that you sent, but I’m not sure if it will produce what I’ve been asked to show. Im so knew it’s taking me a long time to figure out these types of formulas or if I just need to build a report with the summary of that column as average ?
-
First you count the closed and then divide by the count of the total.
=COUNTIFS(Status:Status, "Closed Complete") / COUNT(Status:Status)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome do you do or know of anyone on here that does smartsheet tutoring or can walk me through a sheet / report/ dashboard set up due to the number of hours it’s taking me to figure all this out I rather walk through it with someone the first few times to reduce time.
-
I am not sure of anyone that does it for free. I do know that Smartsheet offers professional services for a cost. I provide professional services for a cost as well.
There are a number of free articles and videos in the learning center and HERE is a collection of onboarding videos/lessons that SS offers for free.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome I am looking to pay for tutoring services. I am walking through the free videos and onboarding but what I need to produce I am thinking working with a tutor is best. If you are available please let me know how to contact
-
I don't offer tutoring services. We do consultancy work (full solution builds/business practices evaluation/etc.). I am not sure of anyone who does offer tutoring.
@Genevieve P. Does Smartsheet offer something that @J25 could take advantage of?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @J25
Yes! We have a number of services that may be helpful for you.
The first thing I would check is to see if your plan already has access to booking Pro Desk sessions. These are 30-minute coaching sessions over a screen share where you can ask your questions and receive direction 1-on-1 with a Smartsheet expert. (See: Pro Desk)
Smartsheet also has a number of Webinars that you can register for, or review any of the past recorded Webinars (SmartStart Onboarding / Smartsheet Advance Webinars / Best Practice Webinars).
Your plan may also have access to the Smartsheet University, where you can go through eLearning courses or Instructor-led training.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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!