Proportion based on percentile

[00alex00]

Hello all,

I have two series of value I need to the proportion to figure out which is the value I should put based on my old valuations.

I am going to explain better.

Based on past series I have the below distribution:

A	52	100,00%
B	51	95,24%
C	43	90,48%
D	40	85,71%
E	21	80,95%
F	17	76,19%
G	15	71,43%
H	11	66,67%
I	7	61,90%
L	5	57,14%
M	2	47,62%
N	2	47,62%
O	1	9,52%
P	1	9,52%
Q	1	9,52%
R	1	9,52%
S	1	9,52%
T	1	9,52%
U	1	9,52%
V	1	9,52%
Z	0	0,00%

<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

While the new value are:

18	100,00%
16	95,00%
15	90,00%
14	85,00%
13	75,00%
13	75,00%
10	65,00%
10	65,00%
9	45,00%
9	45,00%
9	45,00%
9	45,00%
8	35,00%
8	35,00%
7	15,00%
7	15,00%
7	15,00%
7	15,00%
6	0,00%
6	0,00%
6	0,00%

<colgroup><col style="width: 100px"><col width="100"></colgroup><tbody>
</tbody>

I have to assign an old "valuation" based on the percentile.

Example: the new value of 10 is at 65% of the distribution this means that based on the old valuation should between 7 and 11. rounded 11.

another one: the new valuation of 15 it stays at 90% this means that based on the old valuation I should put a value of 43.

Do you have a quick idea of how I can build a formula to get this?

Thank you very much

 

[Swapnil Shah]

Assuming your first table is in Range of A2: D22 and the other table is on the same sheet from range G2:H22, you can apply below Array formula to cell I2 get required result

=IFERROR(INDEX($C$2:$C$22,MATCH(2,(LEFT(H2,FIND(",",H2,1)-1)+0<=LEFT($D$2:$D$21,FIND(",",$D$2:$D$21,1)-1)+0)+(LEFT(H2,FIND(",",H2,1)-1)+0>LEFT($D$3:$D$22,FIND(",",$D$3:$D$22,1)-1)+0),0)),$C$22)

Please note that this is an array formula and hence you need to press Ctrl+Shift+Enter instead on only Enter. drag formula to remaining Cells to complete result.