Compare value to non-linear list and perform calculation

[JoeG]

I have one I can't figure out in Excel. I have values 1-10 listed in A1-A10 then a list of non-linear numbers in cells B1-B10. I want to compare an input value in B11 to B1-B10 and calculate the fractional value from cells A1-A10. For example:

1	500
2	700
3	850
4	1000
5	1500
Input ->	875

A value of 875 should return a value of 3.16. Is there a function to look at the table and do this?

 

[Toadstool]

Hi JoeG,

I'm not sure what you want for entered values below 500 or higher than the B column last number, but otherwise does this satisfy your need?

JoeG.xlsx
	A	B
1	Number	Value
2	1	500
3	2	700
4	3	850
5	4	1000
6	5	1500
7	6
8	7
9	8
10	9
11	10
12	Input->	875
13	Result->	3.166667
Sheet1

Cell Formulas
Range	Formula
B13	B13	=INDEX($A$2:$A$11,MATCH($B$12,$B$2:$B$11,1))+(($B$12-INDEX($B$2:$B$11,MATCH($B$12,$B$2:$B$11,1)))/((INDEX($B$2:$B$11,(MATCH($B$12,$B$2:$B$11,1)+1))-(INDEX($B$2:$B$11,MATCH($B$12,$B$2:$B$11,1))))))

 

[Peter_SSs]

Welcome to the MrExcel board!

Since you have MS365 you could also use the D2 formula below (if you have the LET function). If you do not have the LET function you could use D3.

21 11 10.xlsm
	A	B	C	D
1	1	500		875
2	2	700		3.166667
3	3	850		3.166667
4	4	1000
5	5	1500
6	6	2000
7	7	3000
8	8	4000
9	9	5000
10	10	6000
Calc Fraction

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(D1,B1:B10,A1:A10,,-1)+LET(b,XLOOKUP(D1,B1:B10,B1:B10,,-1),IFERROR((D1-b)/(XLOOKUP(D1,B1:B10,B1:B10,,1)-b),0))
D3	D3	=XLOOKUP(D1,B1:B10,A1:A10,,-1)+IFERROR((D1-XLOOKUP(D1,B1:B10,B1:B10,,-1))/(XLOOKUP(D1,B1:B10,B1:B10,,1)-XLOOKUP(D1,B1:B10,B1:B10,,-1)),0)

 

[JoeG]

Hi JoeG,

I'm not sure what you want for entered values below 500 or higher than the B column last number, but otherwise does this satisfy your need?

JoeG.xlsx
	A	B
1	Number	Value
2	1	500
3	2	700
4	3	850
5	4	1000
6	5	1500
7	6
8	7
9	8
10	9
11	10
12	Input->	875
13	Result->	3.166667
Sheet1

Cell Formulas
Range	Formula
B13	B13	=INDEX($A$2:$A$11,MATCH($B$12,$B$2:$B$11,1))+(($B$12-INDEX($B$2:$B$11,MATCH($B$12,$B$2:$B$11,1)))/((INDEX($B$2:$B$11,(MATCH($B$12,$B$2:$B$11,1)+1))-(INDEX($B$2:$B$11,MATCH($B$12,$B$2:$B$11,1))))))

Thanks. I was trying to make it simple to explain and your solution works for the information I provided. Actually my data set is in reverse order (10 to 0 on value) with progressively smaller numbers assigned from 10 to 0. When I apply this formula to my data I get #N/A return. I copied to a blank sheet and used the same data but in reverse order to verify. Ove of the other solutions below worked but I thank you for the reply

 

[JoeG]

Welcome to the MrExcel board!

Since you have MS365 you could also use the D2 formula below (if you have the LET function). If you do not have the LET function you could use D3.

21 11 10.xlsm
	A	B	C	D
1	1	500		875
2	2	700		3.166667
3	3	850		3.166667
4	4	1000
5	5	1500
6	6	2000
7	7	3000
8	8	4000
9	9	5000
10	10	6000
Calc Fraction

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(D1,B1:B10,A1:A10,,-1)+LET(b,XLOOKUP(D1,B1:B10,B1:B10,,-1),IFERROR((D1-b)/(XLOOKUP(D1,B1:B10,B1:B10,,1)-b),0))
D3	D3	=XLOOKUP(D1,B1:B10,A1:A10,,-1)+IFERROR((D1-XLOOKUP(D1,B1:B10,B1:B10,,-1))/(XLOOKUP(D1,B1:B10,B1:B10,,1)-XLOOKUP(D1,B1:B10,B1:B10,,-1)),0)

D3 did the trick. Thank you!

 

[Peter_SSs]

Actually my data set is in reverse order (10 to 0 on value) with progressively smaller numbers assigned from 10 to 0.

That is a significant difference so not much wonder suggestions fail to deliver.

D3 did the trick. Thank you!

You're welcome. Glad it worked for you ... but given the above, that was pure luck! ?