Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Calculate average of mid-range

    ✭✭✭✭✭
    in Formulas and Functions

    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

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭
      Answer ✓

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

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭
      Answer ✓

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

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

      Your formula worked perfectly!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions