Conditional Percentiles using the Small function, not percentile array function

[SaraB802]

So here is the background to the problem - I have spreadsheet of 19000+ rows of data, multiple calculations including 4 percentile calculations, and obviously excel can't cope. So I have replaced the percentile formulas with SMALL:

=SMALL(M:M,CEILING(Q1*COUNTA(Sheet1!M:M),1))

Cell Q1 has the value 0.85 in it.

The spreadsheet is now working, but the percentiles are for all the AM, not per AM. Here is some sample data.

DTSYS8	CUNO	CUNM	DIVI	SLMN01	SLMN02	SLMN03	SLMN04	IDCD01	Ind code	AM	Status	Open Opportunities	Opps Percentile
20091210	01343	Alpha	G		995			0280	0280	995	Active	50000	2500000
20110407	13909	Bravo	G	529				0200	0200	529	Dormant	25000	2500000
20110629	14000	Charlie	G	529				0200	0200	529	Active	75000	2500000
20110818	01443	Delta	G	529				0280	0280	529	Dormant	3000	2500000
20130712	014730	Echo	G		529			C70	C70	529	Dormant	90000	2500000
20130716		Foxtrot	G		193			0740	0740	193	Prospect	2500000	2500000
20130716		Golf	G		222			0200	0200	222	Prospect	5000	2500000

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

So I want the opps percentile per AM, as you can see it currently shows 2500000, which is for the whole, but what I am after is on an AM. For example for AM 529 the formula should only consider the values 25000, 750000, 3000, 90000, giving a result of 9000.

If anyone can help that would be truly amazing and you would be saving my hair from going grey and my sanity!

 

[markmzz]

If I understand correctly what you want, maybe this array formula (use Ctrl+Shift+Enter and not only Enter) can help you:

=SMALL(IF($K$2:$K$8=K2,$M$2:$M$8),CEILING(0.85*SUMPRODUCT(--($K$2:$K$8=K2)),1))

Result:

DTSYS8	CUNO	CUNM	DIVI	SLMN01	SLMN02	SLMN03	SLMN04	IDCD01	Ind code	AM	Status	Open Opportunities	Opps Percentile
20091210	1343	Alpha	G		995			280	280	995	Active	50000	50000
20110407	13909	Bravo	G	529				200	200	529	Dormant	25000	90000
20110629	14000	Charlie	G	529				200	200	529	Active	75000	90000
20110818	1443	Delta	G	529				280	280	529	Dormant	3000	90000
20130712	14730	Echo	G		529			C70	C70	529	Dormant	90000	90000
20130716		Foxtrot	G		193			740	740	193	Prospect	2500000	2500000
20130716		Golf	G		222			200	200	222	Prospect	5000	5000
************	******	******	******	********	********	********	********	*******	*********	******	*********	*************	***********

<tbody>
</tbody>

Markmzz