Formula for Budget vs Actual vs Difference

[nothere1]

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,

 

[sandy666]

maybe use ABS(Budget - Actual)
you will get absolute values of amount of difference without +/-
or
IF(Budget>Actual , Budget-Actual , Actual-Budget)

 

[nothere1]

Actually, I figured out... here we go.

This is the formula.

=IF(OR(C$6>D$6,D$6>C$6),C$6-D$6,D$6-C$6)

 

[sandy666]

IF(Budget>=Actual , Budget-Actual , Actual-Budget)

did you try this?

change names to proper references

 

[Michael M]

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

 

[nothere1]

Actually Michael, what you have put works as well... However I ran into another issue, take a look. This issue is that it needed to be negative instead of positive. so I had to move the cell value around to get the negative...

Look at line 9 and line 31....as example. Basically if the number in the actual column is smaller than the number in the budget column the number should appear red and negative...

 

[nothere1]

did you try this?

change names to proper references

Let me try that... i have not yet.

 

[nothere1]

did you try this?

change names to proper references

I tried but no luck... getting syntax error...

=IF(Budget>=Actual, [Budget-Actual], [Actual-Budget])

 

[sandy666]

I said change names to proper references
=IF(C3>=D3,C3-D3,D3-C3)
or if this is an Excel Table
=IF([Budget]>=[Actual],[Budget]-[Actual],[Actual]-[Budget])

IF function - Microsoft Support

How to use the IF function in Excel to make logical comparisons between data values.

support.office.com

 

[nothere1]

Thanks, yes already tried that... for the most part that formula works, but not sure how to add another condition to it...