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!!!!!

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!!!!!

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!!!!!

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!!!!!