Function to return a value

[Aspharagus]

Hello Everyone,

I am looking for a function which returns the previous and next value, For example,

If I am looking for 130 which is lies between 120 and 140, The function should return the value 120 and 140.


Thank you.

 

[ExcelToDAX]

Assuming your cell with the value of 130 is in A1. how about:

=LET(val,A1,HSTACK(val-10,val+10))

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Aspharagus]

Hi,

Sorry, I am getting error saying my computer is protected and I cannot install the xl2BB file.
anyway, here is a sample table.
I am trying to create a linear interpolation equation, which calculates the value from 2nd column. the first column represents the angle.
The value I am looking for, can lie anywhere between 0 to 360 degrees. i have provided value upto 90 degree in below table.
Let's say If I want to find value for 32 degree, the function need to calculate the range in which it falls that is 30 and 60.

0	0.723
30	0.523
60	0.633
90	0.661

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F
1	0	0.723
2	30	0.523		32	0.523	0.633
3	60	0.633
4	90	0.661
Data

Cell Formulas
Range	Formula
E2:F2	E2	=XLOOKUP(D2,A1:A4,B1:B4,,{-1,1})
Dynamic array formulas.

 

[Aspharagus]

Hi Fluff,

I have that function, it returns the value of the second column. but is there anyway to get 30 and 60 from first column?

Thanks.

 

[Fluff]

Just change the range in the 3rd argument from col B to col A

 

[Aspharagus]

Wonder why I didn't try that, Thanks Mate, it works!

 

[Fluff]

You're welcome & thanks for the feedback.