display lowest values
 

I have a matrix( 16071 * 100), many of it's cells are empty(blank). i want to
recognize ten lowest values and display with different color on that matrix.
And, empty cells not equal to zero"0",any help will be greatly appreciated.
regards.

display lowest values
 

One play to try ..

Assume the source matrix is in Sheet1!$A$1:$CV$16071

Click Insert Name Define,
then input it as:

Names in workbook: SmallR
Refers to:
=SMALL(Sheet1!$A$1:$CV$16071,{1;2;3;4;5;6;7;8;9;10 })

Then type the source matrix range in the namebox: A1:CV16071
press Enter to select the matrix with A1 active

Click Format Conditional Formatting
Apply the settings below using Formula Is:

Condition 1:
=ISBLANK(A1)
Format Blue

Condition 2:
=ISNUMBER(MATCH(A1,SmallR,0))
Format Brown
Click to OK out

The above will conditionally format all blank cells in the matrix with blue
fill, and all cells matching the ten lowest values with brown fill.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"climate" wrote:
I have a matrix( 16071 * 100), many of it's cells are empty(blank). i want to
recognize ten lowest values and display with different color on that matrix.
And, empty cells not equal to zero"0",any help will be greatly appreciated.

display lowest values
 

Hi max
it's not works,when run it, locks computer,and i want to display 10 lowest
values in different color.
Than'ks

"Max" wrote:

One play to try ..

Assume the source matrix is in Sheet1!$A$1:$CV$16071

Click Insert Name Define,
then input it as:

Names in workbook: SmallR
Refers to:
=SMALL(Sheet1!$A$1:$CV$16071,{1;2;3;4;5;6;7;8;9;10 })

Then type the source matrix range in the namebox: A1:CV16071
press Enter to select the matrix with A1 active

Click Format Conditional Formatting
Apply the settings below using Formula Is:

Condition 1:
=ISBLANK(A1)
Format Blue

Condition 2:
=ISNUMBER(MATCH(A1,SmallR,0))
Format Brown
Click to OK out

The above will conditionally format all blank cells in the matrix with blue
fill, and all cells matching the ten lowest values with brown fill.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"climate" wrote:
I have a matrix( 16071 * 100), many of it's cells are empty(blank). i want to
recognize ten lowest values and display with different color on that matrix.
And, empty cells not equal to zero"0",any help will be greatly appreciated.

display lowest values
 

"climate" wrote:
.. it's not works, when run it, locks computer ..

Sorry to hear that. FWIW, here's a working sample which illustrates:
http://www.freefilehosting.net/download/NDkzNzY=
Display lowest values CF.xls

Note: Do not click direct on the link if you're reading this from MS'
webpage. Do a copy n paste of the entire link, inclusive of the trailing "=",
into your browser address bar.

.. and i want to display 10 lowest values in different color.

The normal CF functionality limits it in xl2003 (my ver), to 3
conditions/colours. Hang around awhile for possible responses from others.
Good luck!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

display lowest values
 

I have a matrix( 16071 * 100)
when run it, locks computer ..

You're trying to apply cf with multiple conditions to over 1.5 million
cells! That would be my guess as to why the computer locks up.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
"climate" wrote:
.. it's not works, when run it, locks computer ..

Sorry to hear that. FWIW, here's a working sample which illustrates:
http://www.freefilehosting.net/download/NDkzNzY=
Display lowest values CF.xls

Note: Do not click direct on the link if you're reading this from MS'
webpage. Do a copy n paste of the entire link, inclusive of the trailing
"=",
into your browser address bar.

.. and i want to display 10 lowest values in different color.

The normal CF functionality limits it in xl2003 (my ver), to 3
conditions/colours. Hang around awhile for possible responses from others.
Good luck!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

display lowest values
 

Hi max
Thank's for your file.
best regards

"Max" wrote:

"climate" wrote:
.. it's not works, when run it, locks computer ..

Sorry to hear that. FWIW, here's a working sample which illustrates:
http://www.freefilehosting.net/download/NDkzNzY=
Display lowest values CF.xls

Note: Do not click direct on the link if you're reading this from MS'
webpage. Do a copy n paste of the entire link, inclusive of the trailing "=",
into your browser address bar.

.. and i want to display 10 lowest values in different color.

The normal CF functionality limits it in xl2003 (my ver), to 3
conditions/colours. Hang around awhile for possible responses from others.
Good luck!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

display lowest values
 

welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"climate" wrote in message
...
Hi max
Thank's for your file.
best regards