+ Reply to Thread
Results 1 to 8 of 8

Calculating Income Surplus/(Deficit) w/ Negative Expense

Hybrid View

  1. #1
    Registered User
    Join Date
    05-02-2018
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Calculating Income Surplus/(Deficit) w/ Negative Expense

    I am trying to find a formula that will handle calculating "Income Surplus/(Deficit)" regardless of Total Operating Expense calculating a positive or negative number. For example:

    Projected YE Income is 5,260,162
    Projected YE Expense is 6,328,910
    Hard Income Surplus/(Deficit) is -1,068,748 (calculated Income - Expense)

    However, Variance Budget vs Projected YE does not work under this calculation:

    Variance Budget vs Projected YE Income is 49,989
    Variance Budget vs Projected YE Expense is -46,444
    Hard Income Surplus/(Deficit) is 96,433 (calculated Income - Expense)
    This number should be 3,546 but requires a calculation of Income + Expense due to the Expense being negative.

    Calculating Hard Income Surplus/(Deficit) should be able to calculate with the same formula regardless if we are talking Actual, Budget, Project, Variance. However, with the simple formula of Income - Expense I can't get there if the variance gives me a negative balance. There has to be some type of conditional format that can be entered across the board that will return the proper balance.

    Help??

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,377

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    You may be able to use the ABS function, which makes any number calculate as if it is positive.

    Otherwise, attach a sample file.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    05-02-2018
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    Apparently I haven't been on here long enough to post a link and I don't see a way to upload a file. Suggestions?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    below the window where you type a reply is "go advanced" click on that and part way down is a hyperlink for manage attachments, click that then go to the place where you have the sample, it is usually easiest if you save it to your desktop as this can be quickest. Upload then close the window then submit.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    05-02-2018
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    An example has been attached.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,856

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    Assuming that all of the values in C77:J77 are correct, I'm not an accountant, the following single formula, pasted into C77 and dragged across, yields the same values:
    Formula: copy to clipboard
    =IF(C74>=0,C18-C74,C18+C74)

    Note that after you drag the formula across you will need to select Fill Without Formatting from the Auto Fill Options box.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    05-02-2018
    Location
    Dallas, TX
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    This did exactly what I was looking for. Thank you so much!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,856

    Re: Calculating Income Surplus/(Deficit) w/ Negative Expense

    You're Welcome and thank you for the feedback. Please take a moment the mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Recurring Income or Expense
    By LewisJ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2017, 07:46 AM
  2. Simple Income & Expense Spreadsheet
    By Bounty Picker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2016, 10:09 PM
  3. [SOLVED] Formula for Calculating Vapor Pressure Deficit
    By mooselodge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2014, 03:33 PM
  4. expense & income calendar
    By chantey88 in forum Excel General
    Replies: 5
    Last Post: 01-09-2014, 10:26 AM
  5. [SOLVED] Calculating weekly income tax on gross income using marginal (bracket) tax rates
    By Puni in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2013, 06:35 PM
  6. Re: Income/Expense Spreadsheet
    By Home in forum Excel General
    Replies: 0
    Last Post: 06-14-2006, 05:00 PM
  7. [SOLVED] How do I set up my expense/income statement in excel?
    By Jean in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 05-05-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1