Sum and average

Hello,

I would like some help. I have tried this formula in many different ways. I have a master sheet that contains entries as a date, added a column to extract the year. Each entry could be located in two locations. I also have the length of stay of each base on the dates (entry date and exit date as well as a running one). I am trying to add and average the length of stay in a certain year in a certain location.

So far I have tried

=IFERROR(AVG(COLLECT({Running Days in WSP}, {Year Admit in WSP Helper}, "2025")) + AVG(COLLECT({1st Rnd Days in WSP}, {1st Rnd Year Admit Helper}, "2025")), 0)

=AVG(COLLECT([Running Days In WSP]:[Running Days In WSP], [In SUD Wellness Program]:[In SUD Wellness Program], true, [Previously in WSP 1st Rnd]:[Previously in WSP 1st Rnd], true, [Previously in WSP]:[Previously in WSP], true, [Location (Bldg No.)]:[Location (Bldg No.)], 1205, [1st Rnd Location (Bldg No.)]:[1st Rnd Location (Bldg No.)], 1205) + COLLECT([1st Rnd Days in WSP]:[1st Rnd Days in WSP], [In SUD Wellness Program]:[In SUD Wellness Program], true, [Previously in WSP 1st Rnd]:[Previously in WSP 1st Rnd], true, [Previously in WSP]:[Previously in WSP], true, [Location (Bldg No.)]:[Location (Bldg No.)], 1205, [1st Rnd Location (Bldg No.)]:[1st Rnd Location (Bldg No.)], 1205))

=AVERAGEIF([Running Days In WSP]:[Running Days In WSP], AND([Location (Bldg No.)]@row = "1205", [Year Admit into WSP Helper]@row = "2025")) + AVERAGEIF([1st Rnd Days in WSP]:[1st Rnd Days in WSP], AND([1st Rnd Location (Bldg No.)]@row = "1205", [1st Rnd Year Admitted Helper]@row = "2025"))

and

=AVG(COLLECT([Running Days In WSP]:[Running Days In WSP], [Year Admit into WSP Helper]:[Year Admit into WSP Helper], "2025")) + AVG(COLLECT([1st Rnd Days in WSP]:[1st Rnd Days in WSP], [1st Rnd Year Admitted Helper]:[1st Rnd Year Admitted Helper], "2025"))

Non of these seem to work. Also the formula is going into a separate sheet.

Thank You in advance for y'alls help.

Find more posts tagged with

Accepted answers

All comments

jjg279

Howdy! Maybe try removing your "" from around 2025; so your first formula would be:

=IFERROR(AVG(COLLECT({Running Days in WSP}, {Year Admit in WSP Helper}, 2025)) + AVG(COLLECT({1st Rnd Days in WSP}, {1st Rnd Year Admit Helper}, 2025)), 0)

Would also possibly recommend removing the "IFError" initially so you can see if it does throw an error (and if so what) or if there isn't anything to average.

Marielitos

@jjg279 , thank you for your help. I do have to break it down a bit more by adding a location of 1205 or 1207, would i just add it.

=IFERROR(AVG(COLLECT({Running Days in WSP}, {Year Admit in WSP Helper}, "2025", {Location}, "1205")) + AVG(COLLECT({1st Rnd Days in WSP}, {1st Rnd Year Admit Helper}, "2025", {1st Rnd Location}, "1205")), 0).

Thanks again

jjg279

That should work! Here is a representative mock-up with for what I think you are after:

I think I would still get rid of the parathesis around your numbers so your final formula would be this:

=IFERROR(AVG(COLLECT({Running Days in WSP}, {Year Admit in WSP Helper}, 2025, {Location}, 1205)) + AVG(COLLECT({1st Rnd Days in WSP}, {1st Rnd Year Admit Helper}, 2025, {1st Rnd Location}, 1205)), 0)

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.