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.
<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!
=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!