Conditional formatting on revenue variances

[mathay2603]

Hi everyone,

I am currently conducting some revenue variance analysis and I'm having a nightmare coming up with a conditional formatting formula that works for each of the 9 lines.

Please see image attached.

Column K, is how I'd ideally like the % revenue variance conditionally formatted. Where it gets particularly tricky is for product 6 and 8.

Product 6 is where I have negative revenue (bad) vs a budget of 0. Now obviously % variance is technically -infinity% but I would like it to show as 0% but have a conditional format of red 0%. But then you have product 8 that has positive revenue of 1000 vs a budget of 0, which is technically infinity% but I would like to show it as 0% but green.

I have written formula (detailed below) that works for every line apart from these two. Does anyone know how I could achieve the above please? Its driving me insane!

In order of priority:

Green = IFERROR(IF(E5<0,(D5-E5)/-E5,(D5-E5)/E5),0)>=0%
Red=(IF(E5<0,(D5-E5)/-E5,(D5-E5)/E5),0)<-5%
Amber =(IF(E5<0,(D5-E5)/-E5,(D5-E5)/E5),0)>-5%


Many thanks!!

 

[mathay2603]

Apologies - picture now shows row numbers (not visible in first image)

Thanks.

 

[Joe4]

Hi everyone,

I am currently conducting some revenue variance analysis and I'm having a nightmare coming up with a conditional formatting formula that works for each of the 9 lines.

Please see image attached.

Column K, is how I'd ideally like the % revenue variance conditionally formatted. Where it gets particularly tricky is for product 6 and 8.

Product 6 is where I have negative revenue (bad) vs a budget of 0. Now obviously % variance is technically -infinity% but I would like it to show as 0% but have a conditional format of red 0%. But then you have product 8 that has positive revenue of 1000 vs a budget of 0, which is technically infinity% but I would like to show it as 0% but green.

I have written formula (detailed below) that works for every line apart from these two. Does anyone know how I could achieve the above please? Its driving me insane!

In order of priority:

Green = IFERROR(IF(E5<0,(D5-E5)/-E5,(D5-E5)/E5),0)>=0%
Red=(IF(E5<0,(D5-E5)/-E5,(D5-E5)/E5),0)<-5%
Amber =(IF(E5<0,(D5-E5)/-E5,(D5-E5)/E5),0)>-5%


Many thanks!!

I think you have some typos here.
Your RED and AMBER formulas are not valid formulas and return errors (invalid formula errors, not calculation errors).
You have too many arguments in your IF statement (you have the ",0" after the FALSE clause). I think maybe you have an IFERROR part of the formula that you accidentally dropped off?

Can you double-check those and post the EXACT formulas you are currently using?

 

[Joe4]

Actually, I am more interested in the formula you are using in column G, can you post that?
I am not sure why Product 4 would return a positive variance, when column D is positive but column E is negative.

 

[awoohaw]

I don't understand your math on products 4,5,7. Negative Budgets with postive/0 actuals ... how can you have a positive percentage increase with a negative budget.
I know that this is somekind of unrealized gain on a budgeted shortfall. but I'm just trying to figure what you want out.
As far as your conditional formatting, to me, you have it way to complicated.
Just do the formatting on your calculated result column:

this is what I have but has the discrepancies i mention in 4,5,7:

mr excel questions 34.xlsm
	C	D	E	F	G	H
2		Revenue Variance Analysis
3
4		Actual	Budget	Variance	% Variance
5	Product1	1950	2000	-50	-2.50%
6	Product2	2000	1800	200	11.11%
7	Product3	-1000	2000	-3000	-150.00%
8	Product4	1000	-2000	3000	-150.00%
9	Product5	-1000	-2000	1000	-50.00%
10	Product6	-1000	0	-1000	-100.00%
11	Product7	0	-2000	2000	-100.00%
12	Product8	1000	0	1000	100.00%
13	Product9	0	2000	-2000	-100.00%
Matthay2603

Cell Formulas
Range	Formula
F5:F13	F5	=D5-E5
G5:G13	G5	=IF(AND(E5=0,D5<0),-1,IF(AND(E5=0,D5>0),1,F5/E5))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:G13	Expression	=AND(G5<0%,G5>=-5%)	text	NO
G5:G13	Expression	=G5<-5%	text	NO
G5:G13	Expression	=G5>0	text	NO

 

[mathay2603]

Actually, I am more interested in the formula you are using in column G, can you post that?
I am not sure why Product 4 would return a positive variance, when column D is positive but column E is negative.

Appreciate your responses!

Product 4 - has sales of 1000 but it was actually budgeted to have negative sales (returns) of 2000. Therefore, you've effectively done 150% better than the target - ie. you've sold 1000 when you expected to have 0 sales and in fact 2000 returns (so -2000 sales). Do you see what i'm getting at here? Sometimes the comparator is negative which when considering sales revenue is you performing positively.

 

[Joe4]

Appreciate your responses!

Product 4 - has sales of 1000 but it was actually budgeted to have negative sales (returns) of 2000. Therefore, you've effectively done 150% better than the target - ie. you've sold 1000 when you expected to have 0 sales and in fact 2000 returns (so -2000 sales). Do you see what i'm getting at here? Sometimes the comparator is negative which when considering sales revenue is you performing positively.

No, that doesn't seem to make logical sense because 2000 in returns does not really equate to 2000 of sales (they appear to be two entirely different things). But objections aside, if that is the way you want it to work, that is fine.

As I asked, I would really like to see the formula you have in column G. I think we can modify it to do what you need. But I need to know what it is right now.

 

[awoohaw]

Appreciate your responses!

Product 4 - has sales of 1000 but it was actually budgeted to have negative sales (returns) of 2000. Therefore, you've effectively done 150% better than the target - ie. you've sold 1000 when you expected to have 0 sales and in fact 2000 returns (so -2000 sales). Do you see what i'm getting at here? Sometimes the comparator is negative which when considering sales revenue is you performing positively.

@mathay2603 , thanks for the eplanation. It is your model, I just wanted to know if the calculation was what you wanted. You've confirmed that.

Regardless, the conditional formatting posted in post #5 should work.

 

[awoohaw]

@mathay2603 , you also do not have a scenario where the actual is less than a negative budget. Would that ever happen?