# Formula for Budget vs Actual vs Difference

#### nothere1

##### New Member
I need to figure out what formula can be used to calculate the difference between the budget vs actual.
What I want to happen is, when I put in the "Actual" number value, it will then in the 3rd column (+ / -) populate as either with a red negative value or a green positive value.

For example, the first line in the picture looks correct, and I used this formula below:

=IF(C3>D3,(-(C3-D3)),(-(D3-C3)))

However, the last line in the picture I used this formula, but didn't get the desired results. The fourth line should show a positive 100 instead of a -100.

=IF(C6>D6,(-(C6-D6)),(-(D6-C6)))

Thank you,

#### Attachments

• Capture.JPG
17 KB · Views: 14

#### sandy666

##### Banned - Rules violations
what condition?
and expected result

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Miki

##### New Member
I think the question I have is similar to this topic, so I'll leave it here.
What I have is a calculation of Committed and Actual to show the difference and then to show that difference in %.
Further, the % should max out at 100% or -100% if the difference is over 100% or -100%, and show numbers (%) as they are in between that range.
I've IFERROR to omit the #DIV/0 error if divided by zero.
The formula I have is working until the point where if there is nothing in Commit column, so zero (0), and Actual is in negative, it's maxing out at positive 100% when actually should max out at negative -100% (marked in yellow in screenshot).

Here is the formula I used.

=IFERROR(IF(AND(D49=0,E49<>0),1,
IF(AND(F49>0,F49/D49>100%),1,
IF(AND(F49>0,F49/D49<-99%),1,F49/D49))),0)

M.

Replies
2
Views
91
Replies
10
Views
269
Replies
3
Views
152
Replies
0
Views
66
Replies
1
Views
282

1,141,704
Messages
5,707,970
Members
421,538
Latest member
Krisco

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back