Conditional format based on percentage of a value

S

Saarek

New Member
Joined
Aug 19, 2014
Messages
31
My spreadsheet has two financial columns. Column A has a financial target in £'s, column B has the amount in £'s that was actually brought into the business.

I want to conditionally format column B to show the colour red if less than 80% of the target value is achieved, amber if 80-99% is achieved and green if 100% or more is achieved.

I know that this can be done with a formula based conditional format with three rules, however the formulas that I have tried have all resulted in the entire column being highlighted in just one colour.

The spreadsheet is an excel 2010 spreadsheet.

Thank you for your help.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Click on the top field in he column ... look at your formulae in the conditional formatting ... if they have $ signs remove them and see what happens
 
Upvote 0
Hello, if I was to remove the $ would that not mean that I'd have to manually apply the conditional format to each cell?

As there are around 500 of these, I'd worry about doing so.

Will test now based on the formula's that I have, although it is probably the formula itself that is at fault as I have tried about 6 variants from the internet.
 
Upvote 0
Hello again WaterGypsy, removing the $ does appear to help. However it only works if my formula is basic, such as =H2><e2 colours="" in="" red="" and="H2">E2 colours in green.</e2>
 
Last edited:
Upvote 0
What formulas are you using?

Also, what columns are the target and actual in? Is it A and B or E and H?
 
Upvote 0
Hello Norie, I've tried so many formulas that I am now lost!

E is the target column and H is the actual taken in column

TargetActual
£15,000£739
£10,000£0
£20,000£3,678
£5,000£747
£15,000£2,371

<tbody>
</tbody>

Have tried $H$2>0.9
 
Last edited:
Upvote 0
The formula(s) you need are simple.

For red:

=$H2/$E2<0.8

For amber:

=AND($H2/$E2>0.8, $H2/$E2<=0.99

For green:

=$H2/$E2>1

To apply the conditional formatting select the entire column of data you want formatted.

PS Change the 2 to reflect the first row of data.
 
Upvote 0
Norie said:
The formula(s) you need are simple.

For red:

=$H2/$E2<0.8

For amber:

=AND($H2/$E2>0.8, $H2/$E2<=0.99

For green:

=$H2/$E2>1

To apply the conditional formatting select the entire column of data you want formatted.

PS Change the 2 to reflect the first row of data.
Click to expand...

Thanks Norie, it is far closer now! Cells with a value of £0 under actual are not going red despite there being a target amount in place.

Do you know how to fix that?
 
Upvote 0
Works fine for me.

By the way the formula for green should be =$H2/$E2>=1
 
Upvote 0
Norie said:
Works fine for me.

By the way the formula for green should be =$H2/$E2>=1
Click to expand...

Thanks for that, Green is working and Amber is working. Red is working about 4 cells out of 5, changed the sales month and now it is not just £0 values but others too. For example one cell has a £5000 target with £627 actual, but it has remained unshaded.

Have checked that all cells have the same format and style, very confusing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Conditional Formatting for multiple cells based on other cell value
Replies
4
Views
293
Benong
B
L
  • Solved
Conditional Formatting Date Ranges
2
Replies
13
Views
666
Joe4
Joe4
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
302
Joe4
Joe4
W
  • Solved
Copying Conditional Formatting with relative values
Replies
9
Views
542
weeksy
W
S
  • Question
Need help with conditional formatting
Replies
1
Views
147
NateSC
NateSC

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top