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:

[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]100,00%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]95,24%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]90,48%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]85,71%[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]80,95%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]76,19%[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]71,43%[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]66,67%[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]61,90%[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]57,14%[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]47,62%[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]47,62%[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]V[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,52%[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0,00%[/TD]
[/TR]
</tbody>[/TABLE]

While the new value are:

[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"></colgroup><tbody>[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]100,00%[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]95,00%[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]90,00%[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]85,00%[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]75,00%[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]75,00%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]65,00%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]65,00%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]45,00%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]45,00%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]45,00%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]45,00%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]35,00%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]35,00%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15,00%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15,00%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15,00%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15,00%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0,00%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0,00%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0,00%[/TD]
[/TR]
</tbody>[/TABLE]

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.