Format minimum value only if unique

**rodewala** · 10-12-2010, 08:22 AM

Here is my situation: I have a set of date in columns A and B. Let's say that the date in column A is 5, 8, 6, 6, 4, and the data in column B is 6, 4, 8, 4, 5. What I want to do is set up a conditional or other formula that will highlight the minimum value in each column, but ONLY if it is the lone (unique) minimum, so in this example I would want the "4" cell value highlighted in column A, but not in Column B since it appears twice in that column. If anyone has any advice on how I can achieve this I would greatly appreciate it. Thanks.

Drew

**NBVC** · 10-12-2010, 08:29 AM

Say your ranges are in rows 1:5 starting in column A

Then select the range(s) and go to Data|Conditional Formatting

Select Formula Is and enter formula:

=AND(COUNTIF(A$1:A$5,MIN(A$1:A$5))=1,A1=MIN(A$1:A$5))

where A1:A5 is the left most range to check and A1 is topleft most cell in your selection

Adjust as necessary.

Click Format and choose from pattern tab.

**rodewala** · 10-12-2010, 09:09 AM

Thankyou so much for your help! Worked perfectly.....don't mean to be a bother, but I still don't fully understand how this formula works....I have some experience with excel, but am not advanced by any means....if you wouldn't mind offering an explanation of how the formula is written I would really appreciate it.....thanks again!

Drew

**NBVC** · 10-12-2010, 09:24 AM

The AND function combines checks for multiple conditions and returns TRUE only if ALL conditions within result in TRUE.

So the first condition is COUNTIF(A$1:A$5,MIN(A$1:A$5))=1

Countif() counts the number of times a condition you specify is met within a range. Here, the condition is MIN(A$1:A$5) which returns the Minimum value from range A1:A5... and Countif() counts how many times the range A1:A5 contains the minimum value found with MIN(A1:A5). After the count is performed and the number of matches returned, it checks to see if that number of matches is equal to 1.. which would indicate that there is only one occurance of the minimum value within the range.

The second condition is a simple one-to-one check A1=MIN(A$1:A$5). This just checks if each cell in the range equals the minimum value of the range.

So if both conditions return TRUE, then TRUE is the result of the AND() and therefore the conditional colour is applied.

Note, in Conditional Formatting the use of relative/absolute referencing is important. The $ denotes absolute referencing (i.e. the reference indicated doesn't change relative to the postion the formula is in).

so A1 applied in conditional format will automatically change to A2 in cell A2 if you had selected multiple cells or if you copy the condition to A2... or it will change to B1 if you selected or copied horizontally.

You can find a lot of tutorials on the web:

Here's one: http://www.contextures.com/xlcondFormat01.html

The A$1:A$5 means keep the row numbers fixed no matter where you copy the conditional formats to... but all changing of column reference A, to B or C or D, etc, depending on where you copied the formula to...