Issue returning a value based on values between two percentage points

[joshmarsh]

Hi I am trying to create a formula that returns the value based on a percentage of the data set below. Where i struggling is returning a value that is not listed below, but is between two numbers. I cant use a straight line allocation because the % increased of values on the right aren't equal compared to the left.

For example, i want to return a value from the right hand column for a 15.55% CAGR... but i need to be able to do this for 0-100% CAGR

TSR CAGR	% of Target PVUs that became Earned PVUs*
20%	200%
19.00%	190%
18.00%	175%
17.00%	160%
16.00%	145%
15.00%	130%
14.00%	115%
13.00%	100%
12.00%	90%
11.00%	80%
10.00%	70%
9.00%	60%
8.00%	50%
7.00%	40%
6.00%	30%
5.00%	25%

 

[denzo36]

Are you trying to lookup the value of 15.55% in column A and return a value from Column B? In this instance, 15.55% returning 130% from Column B?

If so, Vlookup will do this but instead of using an exact match, use approximate match

i.e. =VLOOKUP(YourLookupValue, YourRange,2,1) where 2 is column B and 1 is approximate match instead of 0 for exact match

 

[joshmarsh]

Are you trying to lookup the value of 15.55% in column A and return a value from Column B? In this instance, 15.55% returning 130% from Column B?

If so, Vlookup will do this but instead of using an exact match, use approximate match

i.e. =VLOOKUP(YourLookupValue, YourRange,2,1) where 2 is column B and 1 is approximate match instead of 0 for exact match

Yes, i am looking up the value in column A to return the value from B. However, I would need Column B to scale similar to column A. so in this instance 15.55% should return 134.33%.

screenshot attached to show how the scaling would apply for each value

 

[awoohaw]

Are you asking for the value returned to be interpolated? So from 15-16%, the numbers are 115% and 130%, and if you have 15.55 you want to add .(55/(16-15))*115 to 115%? (I think that is how interpolation works?)

 

[joshmarsh]

Are you asking for the value returned to be interpolated? So from 15-16%, the numbers are 115% and 130%, and if you have 15.55 you want to add .(55/(16-15))*115 to 115%? (I think that is how interpolation works?)

Yes, somewhat. I am looking for a formula that returns the numbers between 15-16% and 130-145%. So by definition, 15% would return 130% and 16% would return 145% but i am looking for a formula that would return the numbers between 15-16%. And it needs to scale according to the rest of the percentages because the allocation between 15-16% is not the same for 14-15%

 

[awoohaw]

OKay, maybe this will work for you:

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	TSR CAGR	% of Target PVUs that became Earned PVUs*		Target	Result
2	20.00%	200.00%		15.55%	138.25%
3	19.00%	190.00%
4	18.00%	175.00%
5	17.00%	160.00%
6	16.00%	145.00%
7	15.00%	130.00%
8	14.00%	115.00%
9	13.00%	100.00%
10	12.00%	90.00%
11	11.00%	80.00%
12	10.00%	70.00%
13	9.00%	60.00%
14	8.00%	50.00%
15	7.00%	40.00%
16	6.00%	30.00%
17	5.00%	25.00%
denzo36

Cell Formulas
Range	Formula
E2	E2	=LET(target, $D2, Tgtlu1, XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,-1,-1), Tgtlu2,XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,1,-1), interppct, (target - Tgtlu1)/(Tgtlu2-Tgtlu1), Lkup1,XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,-1,-1), lkup2, XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,1,-1), DiffLu1Lu2, lkup2-Lkup1, InterpAdd, interppct * DiffLu1Lu2, InterpLookUp, Lkup1 + InterpAdd, InterpLookUp)

 

[joshmarsh]

OKay, maybe this will work for you:

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	TSR CAGR	% of Target PVUs that became Earned PVUs*		Target	Result
2	20.00%	200.00%		15.55%	138.25%
3	19.00%	190.00%
4	18.00%	175.00%
5	17.00%	160.00%
6	16.00%	145.00%
7	15.00%	130.00%
8	14.00%	115.00%
9	13.00%	100.00%
10	12.00%	90.00%
11	11.00%	80.00%
12	10.00%	70.00%
13	9.00%	60.00%
14	8.00%	50.00%
15	7.00%	40.00%
16	6.00%	30.00%
17	5.00%	25.00%
denzo36

Cell Formulas
Range	Formula
E2	E2	=LET(target, $D2, Tgtlu1, XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,-1,-1), Tgtlu2,XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,1,-1), interppct, (target - Tgtlu1)/(Tgtlu2-Tgtlu1), Lkup1,XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,-1,-1), lkup2, XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,1,-1), DiffLu1Lu2, lkup2-Lkup1, InterpAdd, interppct * DiffLu1Lu2, InterpLookUp, Lkup1 + InterpAdd, InterpLookUp)

thank you, this is helpful but only covers between 15-16%. Any ideas for covering the rest of the table %'s?

 

[awoohaw]

Then I don't understand your question.
If you put any percentage from 5% to 20% in D2 it should give a you a new calculation in cell E2.

Please provide some expected results for various values.

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	TSR CAGR	% of Target PVUs that became Earned PVUs*		Target	Result
2	20.00%	200.00%		15.55%	138.25%
3	19.00%	190.00%		15.33%	134.88%
4	18.00%	175.00%		16.50%	152.57%
5	17.00%	160.00%		5.82%	29.10%
6	16.00%	145.00%		6.10%	31.01%
7	15.00%	130.00%		5.83%	29.17%
8	14.00%	115.00%		10.00%	70.03%
9	13.00%	100.00%		14.60%	124.00%
10	12.00%	90.00%		13.47%	106.98%
11	11.00%	80.00%		19.67%	196.65%
12	10.00%	70.00%		18.27%	179.01%
13	9.00%	60.00%
14	8.00%	50.00%
15	7.00%	40.00%
16	6.00%	30.00%
17	5.00%	25.00%
18
denzo36

Cell Formulas
Range	Formula
E2:E12	E2	=LET(target, $D2, Tgtlu1, XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,-1,-1), Tgtlu2,XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,1,-1), interppct, (target - Tgtlu1)/(Tgtlu2-Tgtlu1), Lkup1,XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,-1,-1), lkup2, XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,1,-1), DiffLu1Lu2, lkup2-Lkup1, InterpAdd, interppct * DiffLu1Lu2, InterpLookUp, Lkup1 + InterpAdd, InterpLookUp)

 

[joshmarsh]

Then I don't understand your question.
If you put any percentage from 5% to 20% in D2 it should give a you a new calculation in cell E2.

Please provide some expected results for various values.

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	TSR CAGR	% of Target PVUs that became Earned PVUs*		Target	Result
2	20.00%	200.00%		15.55%	138.25%
3	19.00%	190.00%		15.33%	134.88%
4	18.00%	175.00%		16.50%	152.57%
5	17.00%	160.00%		5.82%	29.10%
6	16.00%	145.00%		6.10%	31.01%
7	15.00%	130.00%		5.83%	29.17%
8	14.00%	115.00%		10.00%	70.03%
9	13.00%	100.00%		14.60%	124.00%
10	12.00%	90.00%		13.47%	106.98%
11	11.00%	80.00%		19.67%	196.65%
12	10.00%	70.00%		18.27%	179.01%
13	9.00%	60.00%
14	8.00%	50.00%
15	7.00%	40.00%
16	6.00%	30.00%
17	5.00%	25.00%
18
denzo36

Cell Formulas
Range	Formula
E2:E12	E2	=LET(target, $D2, Tgtlu1, XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,-1,-1), Tgtlu2,XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,1,-1), interppct, (target - Tgtlu1)/(Tgtlu2-Tgtlu1), Lkup1,XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,-1,-1), lkup2, XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,1,-1), DiffLu1Lu2, lkup2-Lkup1, InterpAdd, interppct * DiffLu1Lu2, InterpLookUp, Lkup1 + InterpAdd, InterpLookUp)

are you altering each formula? i am struggling to get the same result when i change a number. for instance if i change D6 from 6% to 10%, it becomes N/A:

 

[awoohaw]

I think it is because the lookup value is found exactly in the lookup column, so it is coming up with a value of 0 and therefore dividing by zero.