Forum Discussion

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jul 13, 2018

    Hi Terry,

     

    Please try this formula:

    =AVERAGE(SMALL(IF(A1:A20=0, 9.99999999999999E+307,A1:A20),{1,2,3,4,5,6,7,8,9}))

     

    NOTE: To enter this formula, you have to press Ctrl+Shift+Enter at the same time to force the formula to return the correct result, since this formula is a type of array formulas.

    And you have to do this each time you open the formula in the edit mode.

     

    Please find the attached file.

    Regards

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 14, 2018

      As a comment, to avoid CSE that could be

      =AVERAGE(AGGREGATE(15,6,1/($A$1:$A$20<>0)*$A$1:$A$20,{1,2,3,4,5,6,7,8,9}))

Resources