How to sum unsorted number that meets cumulative percentage criteria

[Moatasem91]

Dear Gentlemen,

Could you please help me to adjust below formula to return the sum of value which represent more than 50% rather than the count of rows.

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=MATCH(TRUE,MMULT((ROW(E5:E19)>=TRANSPOSE(ROW(E5:E19)))+0,LARGE(E5:E19,ROW(INDIRECT("1:"&ROWS(E5:E19)))))/SUM(E5:E19)>=50%,0)

Hope this helps!

Example:
Data from largest to smallest:

330
295
260
260
255
235
195
170
160
155
140
135
135
110
105

Cummulative data:

330
625
885
1145
1400
1635
1830
2000
2160
2315
2455
2590
2725
2835
2940

Cumulative percentage:

0.112
0.213
0.301
0.389
0.476
0.556
0.622
0.680
0.735
0.787
0.835
0.881
0.927
0.964
1.000

Therefore, the 50% threshold is reached at the 6th row with 0.556. I want the formula to return the sum which is 1,635.

Thanks again.

[Richard Buttrey]

Here's one way

=SUM(INDIRECT("A1:A"&MATCH(SUM(A1:A15)/2,MMULT(IF(ROW(A1:A15)>=TRANSPOSE(ROW(A1:A15))=TRUE,1,0),A1:A15))+1))/SUM(A1:A15)

[Moatasem91]

Here's one way

=SUM(INDIRECT("A1:A"&MATCH(SUM(A1:A15)/2,MMULT(IF(ROW(A1:A15)>=TRANSPOSE(ROW(A1:A15))=TRUE,1,0),A1:A15))+1))/SUM(A1:A15)

Hi Richard

Thank a lot its seems its return the sum of the cumulative percentage but I need the cumulative number data which is 1,635. Please note that my data is not sorted.

Also could you please mention where can I increase the 50% for example if I want to make 80%


Thanks again.

[Richard Buttrey]

Hi Richard

Thank a lot its seems its return the sum of the cumulative percentage but I need the cumulative number data which is 1,635. Please note that my data is not sorted.

Also could you please mention where can I increase the 50% for example if I want to make 80%


Thanks again.

Hi,

Just leave off the

/SUM(A1:A15) at the end.

For a different % put the % in say C1 and use

=SUM(INDIRECT("A1:A"&MATCH(SUM(A1:A15)*C1,MMULT(IF(ROW(A1:A15)>=TRANSPOSE(ROW(A1:A15))=TRUE,1,0),A1:A15))+1))

[Moatasem91]

Thanks a lot ! you are amazing !

Just remaining the sort as if I not sorted manually from highest to lowest it will give me 660 while I want the highest value 150+140+130+120+110 = 650 (highest 50%)

10 0.83% 50%
20 1.67%
30 2.50%
40 3.33%
50 4.17%
60 5.00%
70 5.83%
80 6.67%
90 7.50%
100 8.33%
110 9.17%
120 10.00%
130 10.83%
140 11.67%
150 12.50%
1200 100.00%