Percentile with two columns

[Charlotte231]

Hello, I am looking for a formula that gives me a threshold/score value from column A up to which the revenue in column B cumulates to 10%. The scores need to be in descending order, i.e. 10 is best and 1 worst. Plus, the data cannot/should not be sorted (otherwise one could use index & match). I have tried the percentile.inc function but that was unsuccessful. Example table attached.

The formula should give me 7, because the revenue of the best scores 9.8, 8, and 7 make up 10% of the toal revenue.

Any help is appreciated.

A Score	B Revenue
8	3
7	2
7	5
6	1
6	3
5	50
3	20
2	5
1	6
9.8	5

<tbody>
</tbody>

 

[JustynaMK]

Hi Charlotte,

If you have your data in columns A and B, use the following array formula in some other column:

=MAX(IF(MIN(IF(SUMIF($A:$A,">="&$A:$A,$B:$B)/SUM($B:$B)<0.1,1,SUMIF($A:$A,">="&$A:$A,$B:$B)/SUM($B:$B)))=SUMIF($A:$A,">="&$A:$A,$B:$B)/SUM($B:$B),$A:$A,0))

Make sure that you press Ctrl+Shift+Enter once you enter it. Let me know if it works for you.

 

[Charlotte231]

Hi Justyna, thanks a lot! That works perfectly.

 

[JustynaMK]

no problem!