how to find address of a range of cells that contain a value closest to a given value?

Are you wanting a worksheet function or a macro? Also, what are the limits on being "close in value"?

Hi Dennis,

I would prefer a worksheet function, but a macro would be fine as well. "Close in value" means the two closest cells that contain a value closest to my given value. First cell that is => my given value and first cell that is =< my given value....please let me know if this makes sense...thanks

also I should mention i would like this to be something that is done dynamically... the values in my spreadsheet are updating in real-time...thanks

I am puzzled why you would want to get such a result - however, the following formula works if the desired value is in cell B1

Please Login or Register  to view this content.

It will fail if the target value is less than the first in the list A1:A9 and it must have a sorted list. For an exact match, only the single cell reference is shown.
Hope this helps.

Simply replace every instance of B1 with H2 and A1:A9 with AH5:AH75 and change the ',1' in the address function to the column number of AH (34).

Please Login or Register  to view this content.

You could use a function to calculate the column of the data

Please Login or Register  to view this content.

and use that instead of the '34'.
Are you using the result in another formula? If so, there may be a better way of getting the required result.

i have attached a sample spreadsheet with my data...when i use the formula it gives the result as AH42:AH43...the correct result is AH46:AH47

exampledata.xlsx


im using the location of these cells in another function that interpolates between the two data points, the values in column AH have corresponding values in column AI (not shown) ...it uses address of cell value above/below my given value to interpolated and find the corresponding AI value at my given value

thanks

Originally Posted by AndyPS

Simply replace every instance of B1 with H2 and A1:A9 with AH5:AH75 and change the ',1' in the address function to the column number of AH (34).

Please Login or Register  to view this content.

You could use a function to calculate the column of the data

Please Login or Register  to view this content.

and use that instead of the '34'.
Are you using the result in another formula? If so, there may be a better way of getting the required result.

Andy,

my response to ur post is above....is there a start row that needs to be entered somewhere in this formula? is that why im getting the incorrect result? (please see above)

Sorry - rushed off an answer and didn't have time to construct sample data. Now that you have posted some data it is a lot easier. The arguments of ADDRESS() are row number and column number - with a further argument that sets the referencing as fully absolute (1) to fully relative (4). We need to add the row offset.
The MATCH() which is the first argument of the first ADDRESS function sets the row with a start value of 1 - thus we add 4 if the range starts in row 5. This also has to be applied to the second ADDRESS function used to get the second data value address.
What you probably need is

Please Login or Register  to view this content.

A combination of VLOOKUP/MATCH/INDEX could be used return the appropriate values from column AI for use in the interpolation; would it not be easier to use those rather than going to cell references that will break if the sheet structure is altered? (There are other performance disadvantages of using CELL("col",AH5) etc for getting the required offsets.)

A linear interpolation would be as per the cells in AK3, AN6 in the attached workbook.

Is this what you need?

Andy,

the interpolation functional im using requires a range of cells to be entered... for example the function requires range of X, range of Y...i could select AM3:AM4 for range of X and AN3:AN4 for range of Y, but i'd rather not have cells on the spreadsheet showing values of range of X and range of Y. is it possible to select the values directly instead of selecting the range of sells that contain values...what im asking is if there is a way to enter the values in AM3:AN4 directly into my interpolation function...so for example (VLOOKUP(H2,AH5:AI75,1,TRUE))INDEX(AH5:AH75,$AL4)) ... for range of X...using the ":" in my example is wrong but i was wondering if there were other alternatives. i could use references...but i guess using the references I will have the problem of references being broken if i add/remove columns, rows etc...

what u have is great ...i was just wondering if there were other ways...thanks for ur help

There are a number of points here and I'll put some thoughts down in almost random order!

OFFSET() returns a range as its result and can be used in functions that take in ranges (e.g. SUM, FORECAST etc.). It is a little tricky to set up as it has several arguments. However it is very useful for getting a variable range - with the downside that it is a so-called volatile function that is always recalculated, causing slow response in very large workbooks.

I am a supporter of having 'helper' cells (or rows, columns) to break a complex formula into smaller steps. This has three benefits: the formulae are easier to understand, they are easier to maintain and often Excel calculates more quickly, especially if values used in several places are calculated once then referenced.

Such helper rows or columns can be hidden or put outside of the usual area (or both) - by the way it is possible to set the scroll area of a worksheet so that the user cannot get to outside areas see e.g. http://spreadsheets.about.com/b/2011...-worksheet.htm

It is possible to set up the MATCH(), INDEX() combination to be robust in the event of structure changes. The functions COLUMN(), ROW() return the numerical values of a cell so could be used and pointed at the cell where your data range starts in order to extract the offsets needed. It does start to look a bit complicated, though and may not be easy to understand/maintain if you revisit it a few months later!

Another way of putting a complex formula into a workbook is to write a user-defined function in VBA. This is good if there is a lot of logic that would be difficult to express in standard Excel functions (e.g. AND, OR, IF x THEN y ELSE) and you are using it in several places. I once wrote one to give the date of Easter in any year (+/- 100 years) to use in working day calculations. However, some experience is needed and so there is a learning curve to climb.

Lastly, there is a lot of useful information in Excel's help facility - I often call it up to remind myself of the syntax of functions - I used it to look up a link for help on setting scrolling rather than writing it myself.

If you want a bit more explanation of these thoughts, please ask. If you post a sample workbook of what you want to do, it is often much easier to see the requirements. All too often people don't explain the limits on the data and what to do in special cases such as the input variable being out of the range of the list. Professional spreadsheets handle rogue data cleanly without causing unexpected or incorrect results.

Kind regards,
Andy

Thanks Andy for the great info. You have provided a lot of great info for me to digest. I can write some basic VBA code...but I'm always learning more. I think it's a good idea to write stuff down like how to modify/maintain the spreadsheet so I don't forget it a few months later. I'll be sure to ask for your help when I have questions on your tips.

Thanks