Looking for assistance with a formula

Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function.

I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list.

I have the following columns I am trying to avg: [County], [Service], [Openings]

I am looking for the avg openings specific to a county and service.

Any help would be greatly appreciated!

Thanks in advance!

Find more posts tagged with

Sheets

Formulas

Reports

Accepted answers

nicksnav

In this calculation can you figure out what values are being averaged? "#divided by zero" is listed as, "Your formula attempted to divide an amount by zero (e.g. 4 / 0 provides a divide by zero error)." However, I can get my formula to work on multiple amounts in the [Openings] column except for empty cells. Is it possible the amounts/values you're averaging in this scenario have empty cells? Can you try a different [County] and [Service] combination?

All comments

Mike Jennings

The way that comes to mind when trying to find an average based on multiple criteria is using the function "collect" in addition to your average function.

=avg(collect({openings column}, {County Column}, "County Criteria", {service column}, "Service Criteria"))

Ella_80

Thank you, I tried this and unfortunately it did not work. :(

nicksnav

This formula should work. If you're displaying the average on the same sheet as your criteria the formula would be.

=AVG(COLLECT([Openings]:[Openings], [County]:[County], "County Criteria", [Service]:[Service], "Service Criteria"))

Try that. If it doesn't work can you display your County and Service criteria?

Ella_80

I can't display my data unfortunately. But I did try this and got a different error message than before "#divide by zero"

Any thoughts?

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.