display lowest values

[climate]

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.

 

[Max]

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.

 

[climate]

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]

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

---

 

[T. Valko]

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.

 

[climate]

Hi max
Thank's for your file.
best regards

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.


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]

welcome.