+ Reply to Thread
Results 1 to 4 of 4

daily/weekly budget calculation (if cell1 is less than 0 take X from cell2)

  1. #1
    Registered User
    Join Date
    01-24-2011
    Location
    Yorkton, Saskatchewan
    MS-Off Ver
    Excel 2003
    Posts
    2

    daily/weekly budget calculation (if cell1 is less than 0 take X from cell2)

    I am trying to create a formula for a budget.
    example:
    I have a daily budget of $20
    I also have a weekly budget of $100

    So If I spend more than $20 in a day I want the amount subtracted from the $100 weekly budget. But I don't want the remainder added to the weekly budget.

    So if on Monday I spent $40, the weekly amount would show $80, On Tuesday I spent $10 the weekly would still show $80.

    So the weekly amount never increases, it only decreases if the daily budget goes into negative numbers.

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: daily/weekly budget calculation (if cell1 is less than 0 take X from cell2)

    If Monday to Sunday are in cells A1:A7 with the daily spend in B1:B7, your weekly budget cell would need to be something along the lines of:

    =100-if(B1>20,B1-20,0)-if(B2>20,B2-20,0)-if(B3>20,B3-20,0)-if(B4>20,B4-20,0)-if(B5>20,B5-20,0)-if(B6>20,B6-20,0)-if(B7>20,B7-20,0)

    or you could add an extra column to show how much over the daily budget you have gone, sum that up and subtract from 100

    C1=if(B1>20,B1-20,0), all the way down to C7

    then weekly budget = 100-sum(C1:C7)

    Hope that helps.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: daily/weekly budget calculation (if cell1 is less than 0 take X from cell2)

    Using the layout outlined above.

    Weekly

    Please Login or Register  to view this content.
    there's nothing to stop the above dropping below 0 of course - unclear what should happen then - you might consider an outer MAX wrapper.

  4. #4
    Registered User
    Join Date
    01-24-2011
    Location
    Yorkton, Saskatchewan
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: daily/weekly budget calculation (if cell1 is less than 0 take X from cell2)

    Thank you very much guys. That solved my problem!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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