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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,323
Messages
5,836,637
Members
430,442
Latest member
Zephreo

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