Hi All,
I often do variance % formulas for costs and revenues, please see below the formula I am currently using. Is there a way that I can adapt the formula so that the cells I have highlighted in red show +100% if it is a positive variance and -100% if it is an adverse variance? I would like E36 to show -100%, and E38 to show +100% for the costs section and in the revenue section I would like J36 to show +100% and J38 to show -100%.
It seems very hard to find a way around this! Any help would be greatly appreciated.
Thanks.
I often do variance % formulas for costs and revenues, please see below the formula I am currently using. Is there a way that I can adapt the formula so that the cells I have highlighted in red show +100% if it is a positive variance and -100% if it is an adverse variance? I would like E36 to show -100%, and E38 to show +100% for the costs section and in the revenue section I would like J36 to show +100% and J38 to show -100%.
It seems very hard to find a way around this! Any help would be greatly appreciated.
Thanks.
Variance ^1 Formulas.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
31 | Costs | Revenue | |||||||||
32 | Act | Bud | Var | Var % | Act | Bud | Var | Var % | |||
33 | 20 | 5 | -15 | -300% | 20 | 5 | 15 | 300% | |||
34 | 5 | 20 | 15 | 75% | 5 | 20 | -15 | -75% | |||
35 | 0 | 20 | 20 | 100% | 0 | 20 | -20 | -100% | |||
36 | 20 | 0 | -20 | 0% | 20 | 0 | 20 | 0% | |||
37 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0% | |||
38 | -1 | 0 | 1 | 0% | -1 | 0 | -1 | 0% | |||
39 | 0 | -1 | -1 | -100% | 0 | -1 | 1 | 100% | |||
40 | -2 | -3 | -1 | -33% | -2 | -3 | 1 | 33% | |||
41 | -3 | -2 | 1 | 50% | -3 | -2 | -1 | -50% | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D33:D41 | D33 | =C33-B33 |
E33:E41,J33:J41 | E33 | =IFERROR(D33/ABS(C33),0) |
I33:I41 | I33 | =G33-H33 |