Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
NP
 
Posts: n/a
Default Closest number match help ...

Thats great. Many thanx.

NP

"Frank Kabel" wrote in message
...
Hi
then try the following array formula
=INDEX(A1:A4,MATCH(TRUE,A1:A4=800,0))

--
Regards
Frank Kabel
Frankfurt, Germany

"NP" schrieb im Newsbeitrag
...
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" wrote in message
...
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))

--
Regards
Frank Kabel
Frankfurt, Germany

"NP" schrieb im Newsbeitrag
...
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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
How to format a number in Indian style in Excel? Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM
What defines number or text Danny J New Users to Excel 3 December 7th 04 08:27 AM
Lookup closest number in list Jeff Excel Discussion (Misc queries) 6 November 26th 04 08:27 PM


All times are GMT +1. The time now is 08:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"