Formula for Budget vs Actual vs Difference

nothere1

New Member
Joined
Oct 29, 2019
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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
    Capture.JPG
    17 KB · Views: 14

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
Joined
Feb 29, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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)

1582976649337.png


Thank you in advance for your help!
M.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top