Highlight Min value in range greater than

cvincent

Board Regular
Joined
Aug 28, 2011
Messages
66
I have a column of percentages, and need to highlight the smallest value that is greater than 80%. This report is updated daily. I would like to know how to set conditional formatting to do this, or at least have a formula or run a macro to do this.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
You could use this array-entered** formula to get that value (just change the range as needed and format the cell the formula is in as a percentage)...

=MIN(IF(B1:B100>80%,B1:B100))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
 

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,952
Office Version
  1. 365
Platform
  1. Windows
Or use this for your conditional format (data in H1:H20) =SMALL($H$1:$H$10,COUNT($H$1:$H$10)-(COUNTIF($H$1:$H$10,">.8")-1))=H1
 

cvincent

Board Regular
Joined
Aug 28, 2011
Messages
66
This worked Rick - thank you so much. Have a good day.


You could use this array-entered** formula to get that value (just change the range as needed and format the cell the formula is in as a percentage)...

=MIN(IF(B1:B100>80%,B1:B100))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
 

cvincent

Board Regular
Joined
Aug 28, 2011
Messages
66
Thank you for your response Ben. I've added Rick's formula to my spreadsheet and it works great.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,842
Messages
5,525,171
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top