conditional formatting formula

[lohj]

I have a column of numbers that have zeroes in it. I want to highlight the lowest non-zero number in blue. Using the 'Bottom 1' rule only highlights the zeroes. Not sure how to implement the 'bottom 1' AND <> 0 in a formula.

 

[T. Valko]

I have a column of numbers that have zeroes in it. I want to highlight the lowest non-zero number in blue. Using the 'Bottom 1' rule only highlights the zeroes. Not sure how to implement the 'bottom 1' AND <> 0 in a formula.

What version of Excel are you using?

Are there any negative numbers in the range?

 

[lohj]

Excel 2007, no negative numbers in the column.

 

[T. Valko]

Excel 2007, no negative numbers in the column.

Try this...

Let's assume the data is in the range A2:A15.

Select the *entire* range A2:A15 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The

formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
• =AND(A2<>"",A2=MIN(IF(A$2:A$15>0,A$2:A$15)))
• Click the Format button
• Select the desired style(s)
• OK out

 

[barry houdini]

Hello lohj, welcome to MrExcel,

Try this approach assuming you have data in A1:A20

Select that range and then use conditional formatting with this formula

=A1=SMALL(A$1:A$20,COUNTIF(A$1:A$20,0)+1)

apply blue formatting > OK

 

[lohj]

Hmmm, neither formula worked. Could it be because there are blanks in the column? The data is for daily weather balloon soundings, occasionally there is no data if the weather balloon malfunctions. The column I use is U, goes from U2:U367 (labeled Z700), the first four of months of data are blank, as we are still inputting historical data, and last June 8 and beyond are blank no data as of yet. But it should have been able to find the lowest number for April and May.

 

[T. Valko]

Hmmm, neither formula worked. Could it be because there are blanks in the column? The data is for daily weather balloon soundings, occasionally there is no data if the weather balloon malfunctions. The column I use is U, goes from U2:U367 (labeled Z700), the first four of months of data are blank, as we are still inputting historical data, and last June 8 and beyond are blank no data as of yet. But it should have been able to find the lowest number for April and May.

Kind of hard to troubleshoot in a forum.

Are you sure the data is numeric data? Even though it may look like numbers it could be TEXT numbers which is not the same as numeric numbers in Excel. Both of the formulas suggested will only work on numeric numbers.

One way to test that the data is true numeric numbers is try some test formulas.

A1 = an entry that looks like a number

=ISNUMBER(A1)
=COUNT(A1)

If A1 is a true numeric number then those formulas will return TRUE and 1 respectively.

 

[lohj]

Barry Houdini's formula worked!

=A1=SMALL(A$1:A$20,COUNTIF(A$1:A$20,0)+1)

I had a typo in the formula. I'd like to know what the =A1=SMALL is doing. Thanks all!

 

[barry houdini]

This gives you the lowest non-zero number (assuming there are no negative numbers as you say)

=SMALL(A$1:A$20,COUNTIF(A$1:A$20,0)+1)

For example if there are 3 zeroes in that range then the 4th smallest number is the smallest non-zero number......that formula gives you that because the COUNTIF formula will return 3 so you get

=SMALL(A$1:A$20,4)

In conditional formatting you want each cell to be compared against that number hence

=A1=SMALL(A$1:A$20,COUNTIF(A$1:A$20,0)+1)

..so in my example if A1 is equal to the 4th smallest number in the range (the smallest non-zero number) then it will be formatted as specified.

Tony's formula should work for you too.......but I'm aware that sometimes "array formulas" used in conditional formatting in Excel 2007 work OK first time but then don't work if the workbook is re-opened......only with SP2

 

[lohj]

Thanks Barry. What if I were to need the lowest 5% non-zero numbers highlighted in light blue, and keep the lowest non-zero number in dark blue, what would be the way to do it? When I make a separate rule for the bottom 5 in light blue, all I get are the zeroes highlighted.