Conditional formatting the minimum amount excluding blanks

[Folander]

Hi all,

I have been trying various formulas without success. I need to conditional format a range of cells, say A1 to G1 to highlight the minimum amount excluding any blanks in that range. I just can't do it and it's doing my head in!

Any help is greatly appreciated.

Fo

 

[Joe4]

Blanks and text should already be automatically excluded by the MIN function.
So you should be able to use a Conditional Formatting formula like:

=MIN($A1:$G1)=$A1

 

[srocks124]

Can you add a column that writes the minimum number and refer to that?

If you add a column and put '=MIN(A1:G1)' in it, it will pick up the lowest number, excluding blanks. (UNLESS ALL ARE BLANKS IT WILL RETURN A 0) AND ALL BLANKS WILL BE HIGHLIGHTED)
Then for the conditional formatting use the 'equal to' and select the cell with the min formula in it.

 

[Joe4]

Can you add a column that writes the minimum number and refer to that?

If you add a column and put '=MIN(A1:G1)' in it, it will pick up the lowest number, excluding blanks. (UNLESS ALL ARE BLANKS IT WILL RETURN A 0) AND ALL BLANKS WILL BE HIGHLIGHTED)
Then for the conditional formatting use the 'equal to' and select the cell with the min formula in it.

No need for an extra column (unless they need an Array function), as you can enter the formula directly in Conditional Formatting like I showed above.

 

[Folander]

Thanks for your responses, but the Min formula in conditional formatting is not excluding blanks. This is what is driving me insane. I'm using Excel 2010

 

[Folander]

I managed to find a workaround. I think an issue I had was the cell I had wasn't blank, but it was the result of 0/1000 for example which would give a crazy low number. So I used this formula nicked off another site: =A1=MIN(IF(A112:G112>0.001,A112:G112," ")) and that worked.

Thanks for your help with the above.

Fo

 

[Joe4]

Yes, zero (or a very low number) is very different than a blank, as the MIN function only includes numbers.
So a blank would be fine for the original equation, but 0 or an tiny fraction would not work, as written.