Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Lowest value ignoring 0 or blank cell
I have 4 adjacent columns of values. For each row, I need to highlight the
lowest value, ignoring any blank or 0 value cells in that row. There are about 1200 rows.......so I need this to repeat to the end of the rows. Any takers? As always, thanks in advance for your kind help and input. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Lowest value ignoring 0 or blank cell
Select the top row, do formatconditional formatting,
formula is and use =A2=SMALL($A$2:$D$2,COUNTIF($A$2:$D$2,0)+1) click the format button and select either pattern or font and change the colour. Copy using format painter to the rest of the cells Adapt to fit your own cell ranges -- Regards, Peo Sjoblom "Marie" wrote in message ... I have 4 adjacent columns of values. For each row, I need to highlight the lowest value, ignoring any blank or 0 value cells in that row. There are about 1200 rows.......so I need this to repeat to the end of the rows. Any takers? As always, thanks in advance for your kind help and input. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Lowest value ignoring 0 or blank cell
Top row formula is:
=B3=SMALL($B$3:$E$3,COUNTIF($B$3:$E$3,0)+1) next row copied via format painter or by copy/paste special - format either one is: =B4=SMALL($B$3:$E$3,COUNTIF($B$3:$E$3,0)+1) As you can see, formula adjusts in first cell reference for row change but does not adjust for balance of formula. What am I doing wrong? Marie "Peo Sjoblom" wrote: Select the top row, do formatconditional formatting, formula is and use =A2=SMALL($A$2:$D$2,COUNTIF($A$2:$D$2,0)+1) click the format button and select either pattern or font and change the colour. Copy using format painter to the rest of the cells Adapt to fit your own cell ranges -- Regards, Peo Sjoblom "Marie" wrote in message ... I have 4 adjacent columns of values. For each row, I need to highlight the lowest value, ignoring any blank or 0 value cells in that row. There are about 1200 rows.......so I need this to repeat to the end of the rows. Any takers? As always, thanks in advance for your kind help and input. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Lowest value ignoring 0 or blank cell
OK..... figured it out and am posting the answer so others will be able to
use it. As with everything, it's setting up the first row that is important but you can't use the wildcard $ sign in the formula as it prohibits the adatation of the formula as you go down the rows. So.... solution. Each cell has to be 'conditionally formatted' by itself. 1st cell 1 row =B3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1) 2nd cell 1 row =C3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1) 3rd cell 1 row =D3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1) 4th cell 1 row =E3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1) Now you can select the entire row and format copy them down the whole column. This works, I did it. Thanks for all the help and good luck with future endeavors. Marie "Peo Sjoblom" wrote: Select the top row, do formatconditional formatting, formula is and use =A2=SMALL($A$2:$D$2,COUNTIF($A$2:$D$2,0)+1) click the format button and select either pattern or font and change the colour. Copy using format painter to the rest of the cells Adapt to fit your own cell ranges -- Regards, Peo Sjoblom "Marie" wrote in message ... I have 4 adjacent columns of values. For each row, I need to highlight the lowest value, ignoring any blank or 0 value cells in that row. There are about 1200 rows.......so I need this to repeat to the end of the rows. Any takers? As always, thanks in advance for your kind help and input. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight lowest price in a row. | Excel Discussion (Misc queries) | |||
showing a cell when it is not blank and ignoring it if it is | Excel Discussion (Misc queries) | |||
highlight lowest value in a list | Excel Discussion (Misc queries) | |||
CF to highlight lowest price in row | Excel Worksheet Functions | |||
Highlight lowest number | Excel Discussion (Misc queries) |