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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A Score
[/TD]
[TD]B Revenue
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]9.8
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!