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

Question

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

Answer 1

For your consideration:

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.

Answer 2

Try

=AVERAGEIF(A1:A100,">="&QUARTILE.INC(A1:A100,3))