Return Closest Value

[JWGoldfinch]

I have a worksheet with data from A1 to F50, and there could be duplicate values, is there a formula that I can use in "Conditional Formatting" that would highlight the cells that are the closest to the value I enter into another cell without going over. Example if I enter 50 I would like to highlight the values in the range A1-F50 that are within a range of 45-50.

 

[Haldoug]

Not a formula, but if the number is entered in A52, then put

=A1-$A$52 in cell A54. Copy A54 to B54:f54, then copy A54:f54 to A55:F103.

The result will have the difference between your number and the data in the worksheet. Conditionaly Format A54:F103 to highlight any numbers that are from -5 to +5 to find any number that are within 5 of your target number, or however close you want to find.

 

[Aladin Akyurek]

I have a worksheet with data from A1 to F50, and there could be duplicate values, is there a formula that I can use in "Conditional Formatting" that would highlight the cells that are the closest to the value I enter into another cell without going over. Example if I enter 50 I would like to highlight the values in the range A1-F50 that are within a range of 45-50.

G1: 50

Select A1:F50.
Activate Conditional Formatting.
Invoke the following formula:

=AND(A1>=$G$1-5,A1<=$G$1-5)

And choose the appropriate format.


Is this what you intended to do?