# Tough conditional formatting question

#### fredalina

##### New Member
Cell A1 contains actual sales %.
Cell A2 contains inventory information by %.
Cell A3 contains inventory pipeline information by %.
Cell A4 contains budget %.

I want to highlight cell A1, A2, or A3 if they exceed budget (A4) by a certain amount. The kicker is that the threshold amount varies depending on the budget %. If the budget % is higher, we want to say 20% above the budget (as in, if the budget is 20% then we would want to highlight anything over 24%). But we don't want to highlight if the budget is 0.5% and we are selling/inventory in the 0.6% range, only if it blows it away. So I have the parameters of if budget is <10% to highlight anything that is the budget + 10% (0.5% + 10% = 10.5%), or if the budget is >=10% then highlight anything that is budget * 120% (12% * 120% = 14.4%).

I know I could create some hidden cells that calculate as a 1 or 0 depending on whether criteria are met and then set the conditional formatting based on that, but this worksheet has about 40 sections of sales/inv/pipeline/budget, so it would require basically duplicating the worksheet with a hidden grid and I'd prefer to avoid that. Is there any way I can do it within the formatting formula itself? (And currently I'm in 2003 but am planning an upgrade next week to 2007, so it's okay if I can do it in 2007 but not 2003).

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### RonB1111

##### Well-known Member
Select cells A1:A3, then select Conditional Formatting, formula based, enter the below formula and format pattern as desired:

=A1>=IF(A\$4<0.1,A\$4*1.1,A\$4*1.2)

Replies
1
Views
64
Replies
11
Views
216
Replies
1
Views
15
Replies
3
Views
78
Replies
5
Views
166

1,130,169
Messages
5,640,545
Members
417,151
Latest member
ChickenTenderer

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