Parent Rows Formula Calculation Problems

I'm having issues getting this formula to work and it seems to be the date that is giving me problems, as the formula works when I remove the date… but that defeats the purpose of the formula.

Basically, I'm trying to calculate the Average % based on criteria in a separate sheet. I've nested the date in the sheet summary with a formula calculating for 6 months (183 days) ago. The range I want is for the last 6 months, so the dates we're considering need to be greater than (or equal to) the date in the 6 months ago box.

=IFERROR(IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN([Average Settlement % last 6 months]@row)), AVG(COLLECT({AHS %s}, {AHS OC FIRM}, CONTAINS([Firm (Search)]@row, @cell), {AHS Style}, CONTAINS(Creditor@row, @cell), {AHS Type}, "Settlement", {AHS Date}, >=[6 Months Ago]#, {AHS State}, STATE@row))), "")

As is, the formula errors out and because of the IFERROR, returns a blank.

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

So is what you are looking to calculate within the sheet summary? Because that is the only place where you can use a sheet summary reference. Otherwise, if you let it error, what is the error that you are receiving?

RobWL

The sheet summary is just giving the date of 6 months ago, if I let it error, it gives a divide by zero.

What I'm trying to do with this range and criterion is to just average the rows where the date is greater than the date 183 days in the past. I previously had this referencing a date reference sheet, but that also errored out.

{AHS Date}, >=[6 Months Ago]#

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.