Explanation
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make sure you use absolute references for both of these ranges in the formula.
This formula is based on the LARGE function, which returns the nth largest value from a range or array of values. The range appears as the first argument in LARGE, and the value for "n" appears as the second:
LARGE(data,input)
In the example, the input value (F2) is 5, so LARGE will return the 5th largest value in the data, which is 110. The formula then compares each value in the data range with 110, using the greater than or equal to operator:
=B4>=LARGE(data,input)
=B4>=110
Any cell with a value greater than or equal to 110 triggers the rule, and the conditional formatting is applied.
Related formulas
Highlight bottom values
Highlight values greater than
Related functions
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
Related videos
How to apply conditional formatting with a formula
Conditional formatting based on a different cell
How to highlight rows with conditional formatting
