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