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

1. ## 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. ## 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. ## 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. ## Re: daily/weekly budget calculation (if cell1 is less than 0 take X from cell2)

Thank you very much guys. That solved my problem!

##### Users Browsing this Thread

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

#### 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