# Actual vs. Budget Variance Percentage

1. ## Actual vs. Budget Variance Percentage

Hello: I need some help with this if statement that would solve for all the combinations for actual vs. budget variance and variance percentages. For instance,

Columns
A B C D

Actual Budget Variance \$\$ Variance %
row:1 100 300 200 66.67%

row: 2 300 200 -100 -50.00%

row: 3 0 100 100 100.00%

row: 4 100 0 -100 -100.00%

row: 5 - - - -

Here are my attempts:

This formula is for the Variance % column D.
=if(A1=0,"100.0",if(b1=0,"-100.0",if(A1<0,(B1-A1)/B1))

I need the formula above to work with all these combinations above.

Any insight would be great!  Register To Reply

2. ## Re: Actual vs. Budget Variance Percentage

Welcome to the forum.

I think you're over-complicating it a bit. Try this instead:
Formula:  `Please Login or Register  to view this content.`

Logic:
If both A1 and B1 are 0, return 0;
if A1 is 0, return 1 (which is 100%);
if A1 is not 0 and B1 is 0, return -1 (which is -100%);
If neither A1 nor B1 are 0, divide B1-A1 by B1.

Assuming that C1 is B1-A1, this would give the same result:
Formula:  `Please Login or Register  to view this content.`

The first part of both formulae, which wasn't there in your original try, is needed because otherwise 0 and 0 would give 100% variance.

Format column D as percentage with two decimal places to get the figures as per your example.

Hope that helps.  Register To Reply

3. ## Re: Actual vs. Budget Variance Percentage

Oh thank you so much.

Any advise on the (variance dollar) formula,

Again, here is my attempt:

=If(A1>B1,A1-B1,if(A1<B1,A1-B1)),

Again, Thank you so much!  Register To Reply

4. ## Re: Actual vs. Budget Variance Percentage

Your formula is essentially just doing 'A1-B1' as both IFs lead to the same.
To get the results you have in your first post, just use this:
Formula:  `Please Login or Register  to view this content.`

If you want the signs reversed, use this:
Formula:  `Please Login or Register  to view this content.`

In your PM*, you ask for -100 | 50 | -50. I'm confused as to why you want this, as the variance from -100 to 50 is 150, not -50. However, if you want it, this will do it:
Formula:  `Please Login or Register  to view this content.`

* IMPORTANT Please take a moment to re-read the Forum Rules, specifically Rule 8: Do not private message forum moderators or members for Excel help. All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members. Thank you.  Register To Reply