nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

AVERAGEIFS or AVERAGE(COLLECT()) Help

megan_do

Hi! I am struggling to get this formula to work correctly. The Smartsheet-AI generated formula is returning #UNPARSABLE and I am not too familiar with Average Collect.

The goal: Average the column "Total Days Open" if the following criteria are met: column "Business Type" is NOT "retail" and column "Division" is "Northern"

The AI-generated formula is

=AVERAGEIF([TOTAL DAYS OPEN]:[TOTAL DAYS OPEN], AND([Division]@row = "northern", [Business Type]@row <> "retail"))

I also tried:

=AVERAGE(COLLECT([TOTAL DAYS OPEN]:[TOTAL DAYS OPEN], [Division]:[Division], "northern", [Business Type]:[Business Type],<>"retail"))

Both are returning as Unparsable. Any help is appreciated!! Thanks!

Weitere Beiträge mit folgender Kennzeichnung finden

Sheets

Formulas

Reports

Sheet Summary

Accepted answers

Paul Newcome

Retry the AVERAGE/COLLECT but instead of AVERAGE use AVG.

=AVG(COLLECT(………….))

All comments

Amy Munro

Be careful with your case sensitive info. Not sure if it is the problem verbal explanation stated Northern, but in formula northern. Maybe check retail too.

Try the below.

=AVERAGE(COLLECT([TOTAL DAYS OPEN]:[TOTAL DAYS OPEN], [Division]:[Division], "Northern", [Business Type]:[Business Type], <> "retail"))

megan_do

Unfortunately, it's still showing as #UNPARSABLE after playing around with capitalizations

Paul Newcome

Retry the AVERAGE/COLLECT but instead of AVERAGE use AVG.

=AVG(COLLECT(………….))

Paul Newcome

I would also not that there are issues with the AI generated formula. Please be sure to use the "thumbs down" and provide feedback so that the back-end can "learn".

The AVERAGEIF syntax does not work like that and does not handle multiple range/criteria sets.

There is no AVERAGEIFS.

AVG/COLLECT is the way to go here, but the function is AVG instead of AVERAGE.

megan_do

THANK YOU!! This worked! Much appreciated!!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.