Conditional format

Blackwell

Board Regular
Joined
Oct 19, 2007
Messages
54
I am trying to condition a format so that when the value in cell

cell J1 which reads 0.46 is more than 54% therefore needs to remain in the black, but J2 is showing 1.56 which is less than 54%, how do I condition the cell to show red if it does not reach 54%.


Cost S.P. S.P. Ex VAT G.P. G.P. % G.P. % @ 54%
J1 0.18 1.35 1.149 0.97 84.33% .46
J2 0.61 1.50 1.277 0.67 52.22% 1.56
J3 0.72 1.60 1.362 0.64 47.13% 1.84

Thank you
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
but J2 is showing 1.56 which is less than 54%

Actually, 1.56 = 156% (which is Greater than 54%)

Perhaps you need to devide the number by 100 to convert it to a percent..

Hope that helps..
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
Guys - if you look at the headings, he's talking about selling price and gross profit and stuff. Not entirely sure how the calc works - but I think he means that 0.46 is more than 54% gross profit %...

Actually, looking again, I think the test in the first line is that the 0.97 GP is bigger than the 0.46 GP @ 54% target.
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023

ADVERTISEMENT

Blackwell - I think what you need is a conditional format where the formula is set to something like:

=$D2<$F2 (setting the font colour to red)

where D2 is your achieved GP and F2 is your target GP.
 

Blackwell

Board Regular
Joined
Oct 19, 2007
Messages
54
Guys, Cornflakegirl,

You are right 0.46 is greater than 54% GP and £1.56 S.P. is less than 54%, what I would like to know ios how do I condition the cell which has £1.56 in to show red as this tells me that the cell is not achieving the required 54% mark up.

I hope this is a bit clearer.

Blackwell
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023

ADVERTISEMENT

Blackwell - see my response x-posted with yours.
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
No problem.

For future reference, you might want to use Excel Jeanie (see the link in Richard's sig) or similar to post a snapshot of your spreadsheet. When tables get all squished like the one you posted, it's quite hard to work out what is going on.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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
Top