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

[JackDanIce]

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

 

[footoo]

Maybe only with VBA.

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

 

[JackDanIce]

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]

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\%;