Conditional Formatting Using Gradient Bars

[Isaac_IEZ]

Hey everyone, thanks for your help with this question. I am working on a report for work and I am trying to give a simple visual to show how close a facility is to meeting their goal and thought I could use conditional formatting instead of adding an additional column to an already large data set.

In one column I have the facility's current number and in the next column that facility's goal. The problem I'm running into is that I have over 100 facilities and they all have different goals so I'm not able to set the max value for the gradient fill to a specific value and instead need to base it off the value in the corresponding goal field. Is there a way to get the gradient fill to represent the progress to towards the corresponding goal? See example below

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Current[/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD]693[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]677[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]214[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]

Is thee a way to get the current column (A) to have a gradient fill based on the goal column (B).

Thanks for your help!

Isaac

 

[Rijnsent]

Hi Isaac,
that's a tricky one... The simple answer is: it's not possible, gradients can only be based on cell values themselves, not on a calculation including the field next to it. Your 2 options are:
-indeed add a column based on A/B -> that is a 0%-100% scale that could have a good gradient coloring
-or manually create some conditional formats, each having the A/B as a function and e.g. have a base color RED, then >0,5 -> ORANGE, >0,75 -> YELLOW, >0,9 -> GREEN. No real gradient, but those 4 colors should give you some insights.
Hope that helps,
Koen

 

[Isaac_IEZ]

add a column based on A/B -> that is a 0%-100% scale

Thanks a bunch for the Koen. I want to make sure that I'm understanding you. I'm confused about the "->" what do you mean by that?

You did answer the bulk of the questions, I didn't think it could be done, but I'd love to lock down the options you suggested for the a work around.

Thanks

 

[Rijnsent]

Hi Isaac,
okay, first give the cells a red background color, next:
-add conditional formatting with a formula =A2/B2>0,5 (assuming your data is located in columns A and B) with background e.g. orange
-add conditional formatting with a formula =A2/B2>0,75 with background e.g. yellow
-add conditional formatting with a formula =A2/B2>0,9 with background e.g. green
You might have to mess around with the order of the 3 conditional formats to get the right color, but that should do the trick.
"then >0,5 -> ORANGE" would mean: have as a condition >0,5 (like above) and have e.g. orange as the result of the conditional format.

Hope that works,
Cheers,
Koen