Conditional formatting: Data bar, no negative, max 100%
Results 1 to 4 of 4

Thread: Conditional formatting: Data bar, no negative, max 100%
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,284
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Conditional formatting: Data bar, no negative, max 100%

    Hi,

    In B4, I have formula =B6-SUM(B7:B8)
    In C4 I have formula = (B6-SUM(B7:B8))/B6 (formatted %)

    Where B6 = 1,000,000, B7 = 200,000, B8 = 400,000

    I wish to achieve the following:

    When C4 is 0%-100% a data bar in B4 for an equivalent % fill

    When B4 is negative to show 100% fill

    I can't figure out how to conditional format this, can anyone help please?

    TIA,
    Jack


  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,567
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Conditional formatting: Data bar, no negative, max 100%

    Maybe only with VBA.

    Why not format negative numbers with no fill ? - then can do without VBA.

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,284
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional formatting: Data bar, no negative, max 100%

    Thanks for replying @footoo - unfortunately I couldn't figure out how to force conditional formatting setup to exclude negative numbers.

    Ended up with a work around that kinda does what I needed but not quite as the initial post asked.

    Basically the formula is for a team to monitor if they hit or exceed their target.

    However, when they exceed it, the numerator becomes negative so the final value is negative which causes this issue.

    Tried using MAX, MIN to put a cap on the value but couldn't get to work, whether in cell formula or in the CF formula or even cell formatting, to prevent the negative, but keep the formula "working" when target was < 100% reached.


  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,567
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Conditional formatting: Data bar, no negative, max 100%

    That's quite a bit different from your original post.

    However, if you just want to show a DataBar for positive values and nothing (or a different color) for negative values :
    - Set the DataBar required then click on "NegativeValue and Axis..." in the edit rule dialog box
    - Set the Fill Color and the Border Color to white (or some other color)

    Tried using MAX, MIN to put a cap on the value but couldn't get to work, whether in cell formula or in the CF formula or even cell formatting, to prevent the negative, but keep the formula "working" when target was < 100% reached.
    • To prevent negatives and return 0 instead :
    C4=IF(B4<0,0,B4/B6)
    • To keep negative values but display 0 :
    Cell format : 0%;0\%;

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •