# Conditional Formatting using Percentage Variance

1. ## 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.

2. ## Re: Conditional Formatting using Percentage Variance

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

3. ## Re: Conditional Formatting using Percentage Variance

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)?

4. ## Re: Conditional Formatting using Percentage Variance

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1