Suppose I have the following datapoints. I would like to extract the cumulative percentage distribution of this set of the three largest values. So first step would be to transform to 100% distribution and secondly summarise the three largest values of the new distribution.
| Data |
|---|
| 0.00 |
| 1.35 |
| 11.05 |
| 24.85 |
| 37.85 |
| 15.40 |
| 6.95 |
| 1.65 |
| 0.25 |
I can calculate the individual percentage point with a simple datapoint / sum of datapoints per row and use =LARGE 1,2,3 on the new column to sum up the values. However, the challenge is to make all calculations in a single cell and just return just the final value.
In this case, the target value would be: 0.2494 0.3804 0.1548 = 0.7849 or 78.48%
Thanks for the help
CodePudding user response：
Wrap a LARGE in SUMPRODUCT:
=SUMPRODUCT(LARGE(A2:A10,{1,2,3}))/SUM(A2:A10)
