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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,673
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,030
Office Version
2016
Platform
Windows
Maybe only with VBA.

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,673
Office Version
365
Platform
Windows
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.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,030
Office Version
2016
Platform
Windows
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\%;
 

Watch MrExcel Video

Forum statistics

Threads
1,101,771
Messages
5,482,822
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top