Setting up an Average Filter across multiple columns

Hello All

I'm new to Smartsheet's and still learning formula.

So hoping someone can help with this for me as I'm struggling to figure out what I'm doing wrong?

I require a sheet that will give me the average number of hours an organizer has spent on a completed project per given month

See above

When creating the formula I have used

=Avg(Collect({hours}, {Organiser}, "Jane", {completed}, ">01/01/2022", "<01/31/2022"))

And all I get is unparseable.

the above {} refer back to the work being done sheet using the refence option. Iv over type the long description to make it easier for me to refer to.

Find more posts tagged with

Formulas

Accepted answers

All comments

Mike TV

@Dave Mex

Try this:

=AVG(COLLECT({hours}, {Organiser}, "Jane", {completed}, >DATE(2022, 1, 1), {completed}, <DATE(2022, 1, 31)))

@Genevieve P. Why do I so often have to write formulas out in SmartSheet because I cannot manually type them out in the forums? When I try to use the greater than right arrow in the forums it changes it to trying to quote someone. It's very inconvenient that SmartSheet's own forums don't allow for formula writing conveniently in a thread. This should get fixed. Is there another way to write a formula out here and being able to use the greater than arrow without causing a problem that I'm not aware of? I understand that if I start a new paragraph out with a greater than symbol that it should start a quote. That's good because no formula starts with a greater than symbol, they start with an equals sign. However, why at any point in the paragraph even right here will it do that? Since these are your own forums can't you change that? It's so annoying.

Genevieve P.

Hi @Mike TV

I completely understand and come across the same frustrations when typing formulas directly into the Community comment box. This happens due to the Rich Text ability in Community; the > symbol is a quick-text way to apply the "quote" rich text feature.

Personally, I type > then copy/paste a space after > so that my next active type happens one character after the symbol (which will not trigger the quote functionality).

Additionally, if you write your formula without a space at the front, like so> , then that also prevents Rich Text from auto-formatting. You can add in the space once the formula is typed out.

As a final option, you could also use the Code Block format to type your formula:

Typing formulas with Code Block on will not trigger the quote format either.

There's information on this at the bottom of this FAQ: How do I use Rich Text when I’m posting a question, discussion, or comment?

Cheers,

Genevieve

Mike TV

@Genevieve P.

Thanks for the reply. I think I will try code block next time. It sounds like the easiest way to do it when I'm getting that quote problem.

Dave Mex

@Mike TV

Thank you for the help!

Got it working now

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.