☰

highlighting cell with value closest to zero

tag-icon • tag-icon microsoft-excel highlighting
highlighting cell with value closest to zero

For example, I have this data 1.5, 1.2, -0.98, 1.01, and I want the cell with the value closest to zero to be highlighted. I tried to create the corresponding rule (see picture) but EXCEL does not color any cell. In my example, the cell with -0.98, since this is the value closest to zero, should be colored.

My first attempt

My first attempt

答案1

here is another formula, it's easier to read, also it'll highlight also 0 (which won't be highlighted by currently posted answer).

=ABS(C2)=MIN(ABS($C$2:$C$11))

enter image description here

答案2

This is the solution created by @JohnSUN, who asked me to provide screenshots of an English EXCEL version. The trick is to use his formula and 'Use a formula to determine which cells to format' as rule type, see screenshot.

JohnSUN's solution.

答案3

This also fixes the issue:

enter image description here

N.B.

  • I've given little twist & used cell references in the formula for Conditional Formatting, which makes it dynamic.
  • Formula in use: =ABS(D31-$E$31)=MIN(ABS($D$31:$D$37-$E$31))
  • Adjust cell references in the formula as needed, as well the Criteria in cell E31.

相关内容