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 |
and thanks for your reply.
