Calculate average of mid-range

I am looking to find the average of the middle 50% of a range - excluding the lower 25% and the upper 25%. I am trying to add the formula into a metric sheet, so the formula would reference another sheet. Here is what I thought would work but does not:

=AVERAGEIF({Permit issued to constr start}, ">= " & PERCENTILE({Permit issued to constr start}, 0.25), {Permit issued to constr start}, "<= " & PERCENTILE({Permit issued to constr start}, 0.75))

Help

Find more posts tagged with

Formulas

Accepted answers

Nathaniel Adamian

Hi Mark,

You have the right idea, you're just running into a few formatting issues. The first problem is you're trying to pass multiple criteria to AVERAGEIF which only accepts one. To pass multiple criteria (simulating an AVERAGEIFS()) you need to use COLLECT() inside AVG(). Additionally, it is not necessary to use quotation marks around your logical operators. Rewriting the formula as follows should work.

=AVG(COLLECT({Permit issued to constr start}, {Permit issued to constr start}, >=PERCENTILE({Permit issued to constr start}, .25), {Permit issued to constr start}, < = PERCENTILE({Permit issued to constr start}, .75)))

All comments

Nathaniel Adamian

Hi Mark,

MarkCep

Amazing, thank you, you just save me some brain cells.

Your formula worked perfectly!

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.