Graded Color Scale Conditional Formatting Function with Relative Access

Hi,

I'm using a graded 3-color scale to format the background color for a cell, based on the value of the cell compared to another cell. For example, on the following budget worksheet, if the actual amount is less than the budget, the background should be green; if the actual amount is more, the background should be red; matching values should have a white background.
Screen Shot 2019-01-02 at 12.13.13 PM.png

This formatting applies to many cells, so I'd like the formula to be generic enough to copy the formatting from cell to cell, keeping the relativity of the formatting. (I'd like to be able to copy the formatting on C2 and apply it to C3, C4, etc.).

Here's what I have so far for my 3-color-scale:

Minimum (green): =INDIRECT(ADDRESS(ROW(), COLUMN()-1))*0.5
Midpoint (white): =INDIRECT(ADDRESS(ROW(), COLUMN()-1))
Maximum (red): =INDIRECT(ADDRESS(ROW(), COLUMN()-1))*1.5


That works for everything besides when the budget and actual are both zero; in that case, it formats the background red, but it should really be white.

My second iteration of the conditional formatting formulas was:

Minimum (green): =IF(INDIRECT(ADDRESS(ROW(), COLUMN()-1))>0, INDIRECT(ADDRESS(ROW(), COLUMN()-1))*0.5, -100)
Midpoint (white): =INDIRECT(ADDRESS(ROW(), COLUMN()-1))
Maximum (red): =MAX(100, INDIRECT(ADDRESS(ROW(), COLUMN()-1))*1.5)

However, with those applied, all the backgrounds end up being white.
Any ideas?

Example:
ConditionalFormattingExample.xlsx

People normally do not respond to posts without sample workbooks because they do not want to have to enter in data that you may already have. So, in the future, if you include a sample workbook, the post is more likely to get a response. However, this one was easy enough to set up. There are two conditions to apply, both of them of type "Use a formula."

The first is B2 > C2 -> Green
The second is B2 < C2 -> Red.

By default if B2 = C2, neither condition applies and cell is left unshaded.

Please try

Minimum (green): =INDIRECT(ADDRESS(ROW(), COLUMN()-1))-MAX($B$2:$B$6)/2

Midpoint (white): =INDIRECT(ADDRESS(ROW(), COLUMN()-1))

Maximum (red):=INDIRECT(ADDRESS(ROW(), COLUMN()-1))+MAX($B$2:$B$6)/2

Happy to see that you have progress
Thank for mark topic solved.