Search code examples
excelexcel-formulaaveragequartile

Excel formula to find the average of bottom 25% from the selection

I have numbers in one column (A1:A100), is there a function to find the average from values that are over 75% ?

For example: = average(A76:A100)

I first thought that =quartile(A1:A100;3) will do the job, but this is a bit different.

In real situation I have much more rows (A1:A41535).

Solution

  • For your consideration:

    enter image description here

    Formula in C1:

    =AVERAGE(TAKE(A1:A6,-(COUNT(A1:A6)/4)))

    Or, more dynamic:

    =LET(r,A1:A6,AVERAGE(TAKE(r,-(COUNT(r)/4))))

    Or, even more dynamic:

    =LET(r,TOCOL(A:A,3),AVERAGE(TAKE(r,-ROUNDUP(COUNT(r)/4,0))))

    The latter would take any amount of numbers in column A:A.