Exclude Some cells from percentile formula

[immohi]

How can I exclude cells having value less than 29 from array(column) which have values between 0 to 100.

I have to calculate percentile from renaming values.

[TABLE="class: grid, width: 64, align: left"]
<tbody>[TR]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD]38[/TD]
[/TR]
[TR]
[TD]51[/TD]
[/TR]
[TR]
[TD]72[/TD]
[/TR]
[TR]
[TD]59[/TD]
[/TR]
[TR]
[TD]45[/TD]
[/TR]
[TR]
[TD]77[/TD]
[/TR]
[TR]
[TD]40[/TD]
[/TR]
[TR]
[TD]76[/TD]
[/TR]
[TR]
[TD]82[/TD]
[/TR]
[TR]
[TD]71[/TD]
[/TR]
[TR]
[TD]68[/TD]
[/TR]
[TR]
[TD]29[/TD]
[/TR]
[TR]
[TD]50[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]53[/TD]
[/TR]
[TR]
[TD]54[/TD]
[/TR]
[TR]
[TD]52[/TD]
[/TR]
[TR]
[TD]20[/TD]
[/TR]
[TR]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 488"]
<tbody>[TR]
[TD]PERCENTILE[/TD]
[TD]92[/TD]
[TD]85[/TD]
[TD]70[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Cut-off[/TD]
[TD]76.56[/TD]
[TD]73.2[/TD]
[TD]66.8[/TD]
[TD]53[/TD]
[TD]47[/TD]
[TD]35.3[/TD]
[/TR]
</tbody>[/TABLE]

formula : =PERCENTILE($A$2:$A$20,0.92).

Expected Result:

[TABLE="class: grid, width: 488"]
<tbody>[TR]
[TD]PERCENTILE[/TD]
[TD]92[/TD]
[TD]85[/TD]
[TD]70[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Cut-off[/TD]
[TD]76.8
[/TD]
[TD]75[/TD]
[TD]69.5[/TD]
[TD]56.5[/TD]
[TD]51.5[/TD]
[TD]46.25[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Andrew Poulsom]

Formula in D2 copied across:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Total		PERCENTILE	92	85	70	50	30	15
2	38		Cut-off	76.8	75	69.5	56.5	51.5	46.25
3	51
4	72
5	59
6	45
7	77
8	40
9	76
10	82
11	71
12	68
13	29
14	50
15	0
16	53
17	54
18	52
19	20
20	65
Sheet1

Cell Formulas
Range	Formula
D2	{=PERCENTILE(IF($A$2:$A$20>29,$A$2:$A$20),D1%)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[immohi]

Formula in D2 copied across:

Excel 2010

	A	B	C	D	E	F	G	H	I
Total	PERCENTILE
Cut-off

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]92[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"][/TD]

[TD="align: right"]76.8[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]69.5[/TD]
[TD="align: right"]56.5[/TD]
[TD="align: right"]51.5[/TD]
[TD="align: right"]46.25[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=PERCENTILE(IF($A$2:$A$20>29,$A$2:$A$20),D1%)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you Sir.