Conditional format

[Blackwell]

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

 

[Richard Schollar]

How do you get 0.46 is more than 54% whereas 1.56 is less than 54%?

 

[Jonmo1]

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]

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]

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]

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]

Blackwell - see my response x-posted with yours.

 

[Blackwell]

Thanks Cornflakegirl,

That works out fine,

Thanks again

Blackwell

 

[cornflakegirl]

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.