how can I find the nearest figure?

  • Thread starter Thread starter Martyn
  • Start date Start date
M

Martyn

Hi,
I have a list of numbers on column A:A with associated text on column B:B
I am doing calculations on another Sheet and come up with a number. I want
to compare this number with the Sheet1 A:A column numbers and find the
closest number to it amongst then. Then I need to find the associated text
for this on Sheet1's B:B text.
Any ideas how to achieve this?
Thanks
Martyn
 
Yes, but don't you think this command misses the point of finding "the
closest number amongst" the numbers in Column A if a total match is not
achieved (which is a case that need to be considered)?.
 
Dave Hawley said:
Vlookup should do the job. E.g
=VLOOKUP(100,$A$1:$B$500,2)

In the above example you numbers would be in Column A and sorted in
Ascending order.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
Click to expand...

Also note that you do need to be careful with VLOOKUP as it will only
produce the nearest number in the list that is less than or equal to your
number. For instance, if your list was 5,10,15,20,25,30 and you were finding
the nearest to 19, VLOOKUP will return 15 - this may not be exactly what you
want.

Also, if your number is smaller than the first entry in the list you'll get
an error so it may be prudent to have a zero as the first entry (assuming
you're not going to be dealing with negative numbers).

Stace.
 
Dave Hawley said:
Stace, why not contribute rather than troll?

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
Click to expand...

Wasn't intended it as a troll. Simply thought that Martyn needed to be aware
of the limitations of VLOOKUP.

I guess I'd tackle it by using VLOOKUP to get the nearest smaller number
then either:
a) finding the next number up using the cell reference of the nearest
smaller + 1 (relies on data being in order) or;
b) using a VBA loop to gradually increment the number I started with,
VLOOKUP that and wait for the answer to change so I end up with nearest
lower and nearest higher.

Looks like your INDEX/MATCH method is a better bet though.

Stace.
 
I've tried the command with a dummy "Range" as below (A1:B7)

2,14 4234
1,89 57
0,99 342
133,45 455
45 334
0,6 344
0,5 645

I Used the formula
=VLOOKUP(1,9;Range;2;TRUE)
to locate 57 on the second column (which should match the nearest figure to
1,9 on column A).
But I came up with 342 which showed that VLOOKUP maches the nearest figure
to 1,9 as 0,99 (which is Wrong).

Martyn
 
On sheet1, in cell C1, enter the following formula:

=ABS(Sheet2!$B$2-Sheet1!A1)

Copy down to the last row of data

On the other sheet, with the comparison number in cell B2, enter the
following formula in cell B3:


=INDEX(Sheet1!$B$1:$B$20,MATCH(MIN(Sheet1!$C$1:$C$20),Sheet1!$C$1:$C$20,0))

If there is a tie, the first name will be returned.
 
Back
Top