Hi,
I am facing a quite complex request I'm afraid I am not able to solve without your help 😕
Basically, I have data with providers and orders amount per providers (2 first columns in my table below).
I need to calculate the percentile (20 and 70) based on the percentage of cumulative sum of order amounts compared to total orders amount.
That is to say for my example :
"20% of my providers (doing the biggest orders), represent 51.43% of my orders, 3600€ in amount."
"70% of my providers (doing the biggest orders), represent 96.43% of my orders, 6920€ in amount."
| Provider Name | Orders amount per provider desc | Cumulative sum | cumulative sum/total orders amount |
| DB NETZ AG | € 3 000,00 | € 3 000,00 | 42,86% |
| BENE RAIL INTERNATIONAL NV | € 1 500,00 | € 4 500,00 | 64,29% |
| POST TELECOM PSF S.A. | € 1 000,00 | € 5 500,00 | 78,57% |
| STILOG IST SAS | € 800,00 | € 6 300,00 | 90,00% |
| KRAUSS-MAFFEI WEGMANN GMBH | € 500,00 | € 6 800,00 | 97,14% |
| CALPAM LUXEMBOURG SARL | € 200,00 | € 7 000,00 | 100,00% |
| Total orders amount | € 7 000,00 | € 7 000,00 | 100,00% |
| | | | |
| percentile 20 % : PERCENTILE.EXC(D2:D7;0,2) | percentile 20 amount : PERCENTILE.EXC(C2:C7;0,2) | | |
| 51,43% | 3 600 € | | |
| percentile 70 % | percentile 70 amount | | |
| 96,43% | 6 920 € | | |
Thanks a lot for your help, much appreciated as always on this forum! AnneSo
