Excel conditional format color scale formula

0
votes

I have a column of data in Excel (column Y) that I'd like to apply a 3 color scale to based on how it compares to a second column (column B). What I would like to have is a green color if it is greater than 10x the value in B, yellow if it is >= 7x the value in B, or red if it is below that. Current formulas are pasted below. The current result is that the cell has no formatting applied.

Column B       Column Y
   20             210
   15              91
   12              52

What I've tried

1
excelconditional-formatting
Answered before: stackoverflow.com/questions/7169299/… - Luuk

1 Answers

2
votes

What you are trying to do cannot be achieved with a 3-color scale conditional formatting, because Excel forces you to use absolute references in the formulas. This means that each cell you are trying to format will be looking at $Y$3 and $B$7 when deciding which format to apply.

Your next stop is "Use a formula to determine which cells to format". You will however not get any fancy color gradient.

enter image description here