Calculate average of mid-range

MarkCep

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

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)))

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)))

MarkCep

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

Your formula worked perfectly!