Tough conditional formatting question

fredalina

New Member
Joined
Sep 14, 2011
Messages
46
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
Joined
Nov 28, 2011
Messages
2,277
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)
 

Watch MrExcel Video

Forum statistics

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