Share via

Conditional Formatting Question - Highlight 2 Closest to Zero

Anonymous
2013-01-18T21:36:17+00:00

Hello,

I have a table that I set up that I want to highlight the 2 values when another field that is calculated is closest to zero.  I have the table set up as follows:

Number Calc 1 Calc 2 Calc 3 Difference of Calc 1 - Calc 3
100000 $5 $5.50 $31.00 ($31.00)
150000 $8.22 $5.45 $36.45 ($28.23)

From the table, I would like to highlight the two cells in the number column when the corresponding field in difference is closest to zero, positive or negative.

I hope this makes sense.  Any insight would help.

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-01-18T22:15:01+00:00

    Hello,

     

    I have a table that I set up that I want to highlight the 2 values when another field that is calculated is closest to zero.  I have the table set up as follows:

     

    Number Calc 1 Calc 2 Calc 3 Difference of Calc 1 - Calc 3
    100000 $5 $5.50 $31.00 ($31.00)
    150000 $8.22 $5.45 $36.45 ($28.23)

     

    From the table, I would like to highlight the two cells in the number column when the corresponding field in difference is closest to zero, positive or negative.

     

    I hope this makes sense.  Any insight would help.

    Thanks!

    Try this...

    Assuming your data is in the range A2:E10.

    Select the range A2:A10

    Conditional Formatting

    Use the "formula option"

    Use this formula:

    =ABS(E2)<=SMALL(ABS(E$2:E$10)-0,2)

    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-01-18T23:03:09+00:00

    Off topic...

    On the Index page it says that "jeeped" made the last post in the thread.

    I don't see a reply by "jeeped" ?

    0 comments
  2. Anonymous
    2013-01-18T23:00:34+00:00

    Thank you.   Worked perfectly!

    You're welcome. Thanks for the feedback!

    Now that I think about it, there's no need for the -0.

    So:

    =ABS(E2)<=SMALL(ABS(E$2:E$10),2)

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2013-01-18T22:24:18+00:00

    Thank you.   Worked perfectly!

    0 comments