help in excel percentile function

[sialraza]

Hi All,

I need a formula for an excel sheet of dimension 63rX740c. All columns have variable data. Some columns have first 10 rows filled, some have 33, 29, 52, 63 rows filled. I need a formula which averages the values of the first 20% cells of that column.

I am using this but it produces error message:

=Average(B1:PERCENTILE(B1:B60,0.20))

So here I wish to take the average of the values from cell B1 (first cell) to the 20th percentile cell of a column which has 63 rows( cell number B12).

Any help will be appreciated.

Regards,

AMD.

 

[AhoyNC]

Try changing your formula to:

=AVERAGE(IF(B1:B60<=PERCENTILE(B1:B60,0.2),B1:B60,""))

<COLGROUP><COL style="WIDTH: 314pt; mso-width-source: userset; mso-width-alt: 15286" width=418><TBODY>
</TBODY>

This is an array formula and needs to be entered with CTRL-SHIFT-ENTER

 

[sialraza]

Try changing your formula to:

=AVERAGE(IF(B1:B60<=PERCENTILE(B1:B60,0.2),B1:B60,""))

<tbody>
</tbody>

This is an array formula and needs to be entered with CTRL-SHIFT-ENTER

Hi, Thanks for the help. This formula doesnt work. What I actually need is that, given the following column:

1
2
3
4
5
6
7
8
9
10

<tbody>
</tbody>

The average of above formula of top 5 cells is 3 (15/5). But this (=AVERAGE(IF(B1:B60<=PERCENTILE(B1:B60,0.5),B1:B60,"")) formula doesnt do that. So, we need the average of the first cell to the cell at 50th percentile cell.

Regards,

AMD.

 

[iyyi]

For 50% try:
=AVERAGE(OFFSET(B1,,,COUNT(B1:B60)*0.5))