+ Reply to Thread
Results 1 to 6 of 6

need formula help with subtracting bills from pay check

  1. #1
    Registered User
    Join Date
    10-16-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    need formula help with subtracting bills from pay check

    Hi I am trying to create a template to have a cell where I can enter my pay like D5 and below in like D7 through D16 list my various monthly bills amounts and have it subtract them from the balance of my pay in d5, I have tried doing this however I keep getting iterative error and it will not let me do it, anyone able to help me? I have search and found subtracting from a fixed number which I have found =2000-sum(d7:d16) which works and doesn't create any error however I would like to be able to be able to enter what ever number instead of a fixed number.

    Thanks

    Tim

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

    Re: need formula help with subtracting bills from pay check

    This works fine, so I'm not sure quite what your issue is:

    =D5-SUM(D7:D11)
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: need formula help with subtracting bills from pay check

    Welcome to Excel Forum.
    After reading your post I think that you are looking to preform an iterative calculation. You would put your income (2000) in D5, then put your expenses in D7:D16. As AliGW suggests, put =D5-SUM(D7:D16) in D18. Now go back to D5 and put in =D18. You will get a circular reference warning and D5 will display a value of zero. Select the file tab > Options > Formulas > check Enable Iterative calculations > change the Maximum Iterations to 1 and press OK*. You should now see the expected value in D5.
    *The steps mentioned above reflect the way this is done in version 2010 and may differ from the way to do this in the 2016 version.
    Note: If you leave Enable Iterative calculations checked it may effect the performance of other worksheets.
    Let me 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.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: need formula help with subtracting bills from pay check

    Just curious .....why would you put D5 =D18?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: need formula help with subtracting bills from pay check

    @John Topley, My interpretation of the OP is that the initial amount will be placed in D5 and, after the expenses in D7:D16 are subtracted, then the balance will be displayed in D5. It is also quiet possible that I misinterpreted the OP.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: need formula help with subtracting bills from pay check

    @JeteMac: you are right re-reading the post.

    A better solution would be put the (Current) balance in D6 so D6 =D5-SUM(D7:D50) to avoid the Circular Reference or changing to iterative calculation.

    D5 is Opening Balance

    D6 is Current Balance

    I run my own spreadsheet to manage my finances and use the above approach: keep it simple!
    Last edited by JohnTopley; 10-16-2016 at 12:12 PM.

+ 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] Monthly calendar of bills - pull out bills for a month
    By Keelin in forum Excel General
    Replies: 8
    Last Post: 04-10-2016, 04:57 PM
  2. To maintain the bills
    By praveenadi in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-07-2016, 02:00 PM
  3. [SOLVED] Bills reminder - missing & required formula(s)?
    By the_trooper47 in forum Excel Formulas & Functions
    Replies: 51
    Last Post: 10-12-2014, 08:37 PM
  4. Bi-weekly budget - formula for entering bills by due date
    By Hstclair05 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 02-01-2013, 06:39 PM
  5. Replies: 1
    Last Post: 12-22-2011, 07:24 PM
  6. Replies: 14
    Last Post: 03-25-2011, 08:31 AM
  7. [SOLVED] Re: More bills
    By Steve in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2005, 06:45 PM

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