# Conditional format

#### Blackwell

##### Board Regular
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.

#### Richard Schollar

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

#### Jonmo1

##### MrExcel MVP
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
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

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

Blackwell - see my response x-posted with yours.

#### Blackwell

##### Board Regular
Thanks Cornflakegirl,

That works out fine,

Thanks again

Blackwell

#### cornflakegirl

##### Well-known Member
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.

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.

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.

### Which adblocker are you using?

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

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