Assistance with Averageif formula
Hello,
The formula below was working great until I was asked to add a new requirment where I need to exclude values that are greater than 14 . Otherwise the average is incorrect if greater then 14 "cells" are included. I tried the Averageifs but got lost trying to figure it out so here is my original formulas sans the new requirment. Thanks you for help!
=ROUND(AVERAGEIF([Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], >0, [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM]))
Answers
-
I would recommend using a AVG(COLLECT formula, which will allow you to keep adding conditions for your avg formula as you see fit. Try this...
=ROUND(AVG(COLLECT([Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], >0, [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], NOT(ISBLANK(@cell)))))
Hope this helps!:)
-
Thanks Brian. I edited my post when you were probably responding. I changed my criteria to greater then 0 but not greater then 14. so basically 1 -14.
So with this edit it would look like this:
=ROUND(AVG(COLLECT([Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], >0, [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], <15)))))
Going to give it a try.
-
I tried to following and I am getting the #UNPARSEABLE error.
=ROUND(AVG(COLLECT([Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], >0, [Turnaround Requestor to CSM]:[Turnaround Requestor to CSM], <15)))))
I am new to Avg(Collect) so I must be doing something wrong.
-
TOO many ))!!!
Working like a charm. Thanks for introducing me to AVG(COLLECT).
Appreciate your help!
-
Glad to hear it, and good catch with the "))"s! I think I had so many due to the not(isblank( formula. I love incorporating collect into formulas whenever possible for flexibility to add more conditions later on, even if I only start out with one condition in my formula. Happy Smartsheeting to you:)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!