Closest number match help ...

[NP]

Looking for a way to find the number that falls closest to another number in
Excel ...

Cell A1 contains value 750
Cell A2 contains value 1500
Cell A3 contains value 3000

When I enter a value in cell A5 (e.g. 800), the result I would like retuned
in cell B5 is the number from cell a1, a2 or a3 which is closest to the
value in A5. Anyone any ideas how to do this please?

Many thanks,

NP.

 

[Frank Kabel]

Hi
try the followung array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A4,MATCH(MIN(ABS(A1:AA4-A5)),ABS(A1:A4-A5),0))

 

[Jason Morin]

Try:

=INDEX(A1:A3,MAX(IF(ABS(A5-A1:A3)=MIN(ABS(A5-A1:A3)),ROW
(A1:A3))))

Array-entered, meaning press ctrl/shift/enter.

HTH
Jason
Atlanta, GA

 

[mzehr]

Hi,
I addapted a formula from John Walkenbach
Name A1-A3 Data
Name A5 Target

Enter this formula as an array (Shift-Ctrl-Enter
=INDIRECT(ADDRESS(ROW(data)+MATCH(MIN(ABS(target-data)),ABS(target-data),0)-1,COLUMN(data)+0))

Note that if two numbers are the dame distance from your target it will
select the first one

Hope this helps

 

[hgrove]

mzehr wrote...
...

Enter this formula as an array (Shift-Ctrl-Enter)
=INDIRECT(ADDRESS(ROW(data)+MATCH(MIN(ABS(target-data)),
ABS(target-data),0)-1,COLUMN(data)+0))

...

More proof that INDIRECT(ADDRESS(...)) is almost always a mistake.

First off, if target < MIN(data), this will return #N/A. A strong
argument could be made that it should return MIN(data). Second, this
doesn't require an array formula if data is sorted. The OP's sample
data was sorted in ascending order. If that's always so, the following
non-array formula should work.

=IF(trgt<MIN(data),MIN(data),INDEX(data,ROUND(TREND({1;1.9999999999999},
OFFSET(data,MATCH(trgt,data)-1,0,2,1),trgt),0)))

 

[NP]

Thanks for that. However, I forgot to also mention that I would like the
value returned to be higher. For example ... if A5 contains 800 then I would
like the value returned in B5 to be 1500.

Any ideas please?

Many thanks,

NP.

 

[Frank Kabel]

Hi
then try the following array formula
=INDEX(A1:A4,MATCH(TRUE,A1:A4>=800,0))

 

[NP]

Thats great. Many thanx.

NP

Hi
then try the following array formula
=INDEX(A1:A4,MATCH(TRUE,A1:A4>=800,0))