Variances with % Issue

[Mr Sloth]

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

 

[kevin9999]

Have you tried: =(Actual-Budget)/ABS(Actual)

 

[Mr Sloth]

Have you tried: =(Actual-Budget)/ABS(Actual)

Thanks for reply. I've got a formula for both:

Problem1:=A2/ABS(B2)
Problem 2: =IFERROR(A2/B2, SIGN(A2)*100%)

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

 

[kevin9999]

You could combine the 2 in an IF formula. Assuming that the 1st formula 'kicks-in' when the Budget is 0 - and the Budget figure is in B2 - then this should work:

=IF(B2=0,A2/ABS(B2),IFERROR(A2/B2, SIGN(A2)*100%))

 

[Mr Sloth]

Thanks guys for help, I found =IFERROR((F8/ABS(E8)),1) to work.

The only issue with it is that when both actual and budget are 0 it won't say 0%.