Thread: Conditional Formatting using Percentage Variance

Trying to complete conditional formatting across a range of cells comparing actual to plan

Here are the following rules I would like to use:

Plan - A1
Actual - B1

((B1-A1)/B1)

If actual percentage variance is <=0 fill B1 green
If actual percentage variance is >=0 <=0.05 fill B1 yellow
If actual percentage variance is >0.05 color fill B1 red

If you can help it would be appreciated.

Originally Posted by protonLeah

Conditional Format B1:
If actual percentage variance is <=0 fill B1 green ---> =ROUND((B1-A1)/B1,2)<0
If actual percentage variance is >=0 <=0.05 fill B1 yellow ---> =AND((B1-A1)/B1>=0,ROUND((B1-A1)/B1,2)<=0.05)
If actual percentage variance is >0.05 color fill B1 red ---> =ROUND((B1-A1)/B1,2)>0.05

Thanks Ben that worked. Can you tell me how to extend it across a range of cells (12 months)?