Home |
Search |
Today's Posts |
#1
|
|||
|
|||
find nearest help
Hi there
I have a long list of calculations of which i need to find the nearest match. I can find the nearest elow with VLOOKUP but not the closest. Have tried.=INDEX(list,MATCH(MIN(ABS(list-target)),(ABS(list-target)),0)) but this returns a #N/A error. The nature of the spreadsheet means there can be more than one same answer. I only need to return one of these. Waht is the easiest way to do this??? cheers nobby |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find nearest help
The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER... Hope this helps! In article , nobbyknownowt wrote: Hi there I have a long list of calculations of which i need to find the nearest match. I can find the nearest elow with VLOOKUP but not the closest. Have tried.=INDEX(list,MATCH(MIN(ABS(list-target)),(ABS(list-target)),0)) but this returns a #N/A error. The nature of the spreadsheet means there can be more than one same answer. I only need to return one of these. Waht is the easiest way to do this??? cheers nobby |
#3
|
|||
|
|||
Thanks for replying but that doesnt do it.
See if it helps if I explain a little more! My list address is r2:r271 and my target is a27. there are answers in the list calculation I am working on of -21.05 / 15.22 / 22.96 / 30.70 (unfortunately almost 200 of each as I started with a simple calculation!!) they are sorted in order. =vlookup(a27,r2:r271,1) returns 15.22 (the answer i need is 22.96 a27 total being 22) =index(r2:r271,match(min(abs(r2:r271-a27)),(abs(r2:r271-a27)),0)) returns #N/A even when i ctrl shift enter The calc steps follow through as =index($r$2:$r$271,match(43.054469292902,43.054469 292902,0) =index($r$2:$r$271,#N/A) I can see where its wrong but dont know enough about this function to see why? (To be honest cant see how its supposed to work!?&£) I thought i also may be able to remove the multiple entries (the results are identical) but i need a way I can do this that will keep the columns in line and ignore blank entries. cheers nobby Last edited by nobbyknownowt : April 20th 06 at 10:10 AM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find nearest help
I don't know why you're getting #N/A. The formula seems fine and should
return 22.96... In article , nobbyknownowt wrote: Thanks for replying but that doesnt do it. See if it helps if I explain a little more! My list address is r2:r271 and my target is a27. there are answers in the list calculation I am working on of -21.05 / 15.22 / 22.96 / 30.70 (unfortunately almost 200 of each as I started with a simple calculation!!) they are sorted in order. =vlookup(a27,r2:r271,1) returns 15.22 (the answer i need is 22.96 a27 total being 22) =index(r2:r271,match(min(abs(r2:r271-a27)),(abs(r2:r271-a27)),0)) returns #N/A even when i ctrl shift enter The calc steps follow through as =index($r$2:$r$271,match(43.054469292902,43.054469 292902,0) =index($r$2:$r$271,#N/A) I can see where its wrong but dont know enough about this function to see why? (To be honest cant see how its supposed to work!?&£) I thought i also may be able to remove the multiple entries (the results are identical) but i need a way I can do this that will keep the columns in line and ignore blank entries. cheers nobby |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find nearest help
nobbyknownowt wrote...
.... My list address is r2:r271 and my target is a27. .... =index(r2:r271,match(min(abs(r2:r271-a27)),(abs(r2:r271-a27)),0)) returns #N/A even when i ctrl shift enter The formula above is just what you're typing into your newsgroup messages, not a copy of what you're entering in Excel. Excel would have converted all the lower case letters to upper case. Maybe the formula above is a true copy, but maybe not. Are there any nonnumeric entries in R2:R271? The calc steps follow through as =index($r$2:$r$271,match(43.054469292902,43.05446 9292902,0) =index($r$2:$r$271,#N/A) .... You can't step through the calculations. The ABS(R2:R271-A27) term would return an array that's almost certainly too large for Excel to display in the formula bar. And, FWIW, MATCH(x,x,0) will always return #N/A when x is just a number. The 2nd argument to MATCH need to be a range or an array, so MATCH(x,{x},0) is necessary in order for MATCH to return 1. Does the *array* formula =MATCH(MIN(ABS(R2:R271-A27)),ABS(R2:R271-A27),0) also return #N/A ? |
#6
|
|||
|
|||
Doh!
Embarrassed entry. Have just found out how to type ctrl shift enter while still in formula bar not on cell. sorry to waste your valuable time!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use vlookup or other to find the nearest values (<) or interpola | Excel Worksheet Functions | |||
Rounding to the nearest quarter (decimal) in a Pivot table | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Worksheet Functions | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |