1

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

5
  • What about formula like as =ABS(P74)=1/MAX(ABS(IFERROR(1/$P$74:$P$79;0)))?
    – JohnSUN
    Commented Jul 19, 2022 at 11:21
  • No, this does not work either.
    – Zorg
    Commented Jul 19, 2022 at 11:24
  • Once more - not "cell value" but "formula" (last position in list of options)
    – JohnSUN
    Commented Jul 19, 2022 at 11:35
  • Sorry I retried it and it worked! Maybe write an answer so I can accept it
    – Zorg
    Commented Jul 19, 2022 at 11:36
  • I don't have Excel with an English interface right now, so I won't be able to take the necessary screenshots. Feel free to post your answer and I'll upvote it.
    – JohnSUN
    Commented Jul 19, 2022 at 11:41

3 Answers 3

3

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

1
  • ,, nice one +10 ☺ Commented Jul 20, 2022 at 9:41
4

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.

1
  • 1
    ,, nice one +10 ☺ Commented Jul 20, 2022 at 9:41
-1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .