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