Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Purfleet
 
Posts: n/a
Default Selecting nearest under in a list

I have a list of numbers and i need to either highlight (using conditional
formatting)or display in another cell the nearest number under 10000

i.e

9854
9752
11254
10987
9974 <---------
10025

Help!!!!!
  #2   Report Post  
N Harkawat
 
Posts: n/a
Default

if your values are in column A then on the column B type this
=IF(A1=MAX(IF(A1:A6<10000,A1:A6)),A1,"")
and array enter it (Ctrl+shift+enter) instead of enter

This will place the value that is closest to 10K on column B


"Purfleet" wrote in message
...
I have a list of numbers and i need to either highlight (using conditional
formatting)or display in another cell the nearest number under 10000

i.e

9854
9752
11254
10987
9974 <---------
10025

Help!!!!!



  #3   Report Post  
Domenic
 
Posts: n/a
Default

Try...

=MAX(IF(A1:A6<10000,A1:A6))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Purfleet" wrote:

I have a list of numbers and i need to either highlight (using conditional
formatting)or display in another cell the nearest number under 10000

i.e

9854
9752
11254
10987
9974 <---------
10025

Help!!!!!

  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

One way

=MAX(IF(A1:A6<=10000,A1:A6))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom


"Purfleet" wrote in message
...
I have a list of numbers and i need to either highlight (using conditional
formatting)or display in another cell the nearest number under 10000

i.e

9854
9752
11254
10987
9974 <---------
10025

Help!!!!!



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
LOOP - Adddress List -to email Paul. Excel Discussion (Misc queries) 0 April 13th 05 09:54 AM
Refresh a Validation List? jhollin1138 Excel Discussion (Misc queries) 3 February 17th 05 06:48 PM
Extracting Values on one list and not another B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 02:48 PM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM
Selecting an Item from a List and getting a different item to pop. Matt Excel Worksheet Functions 1 December 7th 04 03:37 PM


All times are GMT +1. The time now is 10:56 AM.

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"