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]))

Find more posts tagged with

Formulas

Accepted answers

All comments

brianschmidt

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!:)

Christina C

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:

Going to give it a try.

Christina C

@brianschmidt

I tried to following and I am getting the #UNPARSEABLE error.

I am new to Avg(Collect) so I must be doing something wrong.

Christina C

@brianschmidt

TOO many ))!!!

Working like a charm. Thanks for introducing me to AVG(COLLECT).

Appreciate your help!

brianschmidt

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:)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.