Cross-posted at: ExcelSloth 01:54 PM Today

Hi All,

Hoping someone could help me please, would really appreciate it.

I am an accountant and often have a % variance column when comparing Actual results against Budget. However, I have come across 2 issues:

1. If I start the variance percentage formula with '=+' if the budget number is a negative and the actual number is a positive, the percentage will be a minus percentage instead of a plus and if both numbers are minus, e.g. actual is -1 and budget is -5 it will show a negative percentage instead of a positive. To fix this, I've always had to keep changing the start of formula to =- or =+ depending on the numbers.

2. If I'm comparing an actual number of say 50 and a budget number of 0 the percentage will show the '#DIV/0!' sign instead of +100% and if I'm comparing an actual number of say -50 and budget number of 0 it will show the '#DIV/0!' instead of -100% so I always have to manually type in 100% or -100%.

I have found a formula to fix problem 1 with the 'ABS' function and have found a formula for problem 2 with IFERROR.

However, is there a way I can combine these 2 formulas together?

Many thanks in advance!!

Chris

Hi All,

Hoping someone could help me please, would really appreciate it.

I am an accountant and often have a % variance column when comparing Actual results against Budget. However, I have come across 2 issues:

1. If I start the variance percentage formula with '=+' if the budget number is a negative and the actual number is a positive, the percentage will be a minus percentage instead of a plus and if both numbers are minus, e.g. actual is -1 and budget is -5 it will show a negative percentage instead of a positive. To fix this, I've always had to keep changing the start of formula to =- or =+ depending on the numbers.

2. If I'm comparing an actual number of say 50 and a budget number of 0 the percentage will show the '#DIV/0!' sign instead of +100% and if I'm comparing an actual number of say -50 and budget number of 0 it will show the '#DIV/0!' instead of -100% so I always have to manually type in 100% or -100%.

I have found a formula to fix problem 1 with the 'ABS' function and have found a formula for problem 2 with IFERROR.

However, is there a way I can combine these 2 formulas together?

Many thanks in advance!!

Chris

Last edited by a moderator: