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

Time:12-20

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

CodePudding user response:

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.

CodePudding user response:

Try

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

  • Related