if And Formulas

Copper Contributor

I make up a monthly budget for my home. One spreadsheet has the weeks across top with two columns for each (payments and salary). Then I list all my bills/expenses on the left most column. I then go across the weeks and "spend" the salary until it's gone. What I want to do is create a formula that will automatically recognize when I've spent all the salary and pay only the remainder of that payment in that weekly column and then move to the next weekly columns and start from there with the remainder and "spend" that week's salary until it's zero. So what I need is an If AND that will see if the salary left is above $0 AND greater than the payment. If so then subtract payment from total; if not then subtract what is left (thus if 200 is due but only 100 is in left subtract 100).
I've figured that out. But when I go to the next week, I need to be able to weed out the paid items and then start at that 100 payment and only take 100 out and proceed to next payment. 

Anyone help with the type of IF AND to use? 

Here is current formula that works great for first week:

=IF(AND(C10>0,C10>$M11),$M11,C10) (C10 is running total; M11 is total payment due in final column).

 

Thanks

7 Replies

@HSBell62 

 

A picture, the saying goes, is worth a thousand words.

 

In the case of an Excel spreadsheet (one that already exists) a copy of the spreadsheet is probably worth, oh, ten thousand words.

 

Your description is no doubt accurate; it's still tricky to visualize. So if you could post a copy of your sheet, that would be really helpful. Feel free to delete anything that would identify you or reveal confidential information.

@mathetes 

Here is the spreadsheet. Column M comes actually from a different page, but I've just entered the numbers in it. Column L is a "check" to make sure that I've paid the correct amount. 

I started my IF AND formula in ROW 11 with Charter and want it to work from there on down and across. 

@HSBell62 

 

I got the spreadsheet. It's going to take me some time to grasp the logic here of what you're trying to do, and the Fourth of July weekend doesn't lend itself to a lot of time....I hope you can be patient.

 

I will add that I've never seen a budget sheet set up in this fashion, which is part of the conceptual difficulty....where did you get the conceptual framework for this? Was it by chance a method you (or some forebear) used on a ledger sheet? I ask in part because, although you're using Excel to do some adding and subtracting, as well as to do the conditional that you've asked about, you don't appear to be taking advantage of some of Excel's abilities to take a simple table of transactions and produce (through such tools as the Pivot Table) a nice cross-tabulated summary of expense by month (or by week, if desired) by budget category. Instead you're tracking things at a very granular level (payee) rather than category (e.g., utilities, entertainment, auto...).

 

So if you have time, maybe you could explain a bit of the larger picture here....how do you see this help manage expenses? Have you ever considered a different approach?

 

By the way, in the sheet you posted, columns L and M are identical. You said you copied the figures for M from another sheet, but since they're the same, one wonders "Why"?

@HSBell62 

 

If I interpret correctly, the result you are trying to return with the formula is the cost in column M, provided there is a large enough balance to cover it.  If the balance is not large enough to cover then you need to return the remaining balance as the result (unless balance is zero, whereby result will be zero).  Below formula (entered in B11) should help with this (I have updated for rows 11+ in the attached workbook):

 

=IF(C10=0,0,IF($M11<C10,$M11,C10))

@HSBell62 

 

Here's a variant on the solution offered by @Charla74 

 

It uses the IFS function rather than nesting IF within IF. Otherwise the same. Perhaps easier to decipher.

=IFS(C10=0,0,$M11<=C10,$M11,$M11>C10,C10)

 

When using IFS, you do need to be aware that the sequence of condition+result is important, in that once a condition is met, the function stops evaluating. So, in this case, if C10=0, then the result is 0 and no further conditions are evaluated. Other than that, the advantage of IFS over nested IF functions is that it follows a simple IFS(condition,result,condition,result,condition,result....) [for as long as needed; no need to count parentheses]

 

Please do come back and let the two of us know if this is working. And if you have time, I really would appreciate your answers to the questions I asked in my most recent posting in this thread, those dealing with how you came up with this method in the first place; it truly is quite unusual when it comes to budget and expense tracking, for some of the reasons I mentioned.

@mathetes @Charla74 

 

Thanks for all the help. Here is the final solved issue. May not be the best way to do it, but it works! Spent a couple of  hours using your suggestions. Then kept playing with it so that it would continue to carry across and put in correct figures.

 

@mathetes  you asked where I got the spreadsheet from. I created it while working with Dave Ramsey on budgeting. With COVID19 I'm at home and teleworking and took a couple courses on Google sheets and Excel. Decided there should be a way to fill in that one sheet each month automatically. Thanks to you all I have it now. Should save about 15-20 minutes a month.  Because invariably I mess up a number or something and things don't add up correctly. 

 

So thanks for the help!  

 

@HSBell62 

 

The Dave Ramsey reference helps a bit make sense of this (e.g., it's not usual to see "tithe" at the top of such an array; but it is commendable!)...

 

I think the other somewhat puzzling aspect is that it would appear (and I'm making this as a tentative observation, seeking your confirmation or correction)...it would appear that rather than tracking expenses after the fact, what you're seeking to do is forward looking to discipline yourself to do all your spending within your means.

 

Most budget spreadsheets approach the task more as a tracking after the fact...which gives you the opportunity to realize (as our did for us) "Wow, look at how much we spent on restaurants last year!" and, based on that realization, the opportunity to change behavior.

 

One Excel method you might want to consider implementing is the use of named ranges to make your formulas more intelligible. As an example, instead of the formula in cell B5 reading =IF(B3=0,0,244) -- which is an example of what's called "hard coding" of a variable--if you were to create a table (perhaps on a tab or sheet of its own) in which you Name the variable "Tithe" and associate whatever value you want, that would enable you to change the amount of the tithe in one place rather than in every formula that incorporates it. The formula then would read =IF(B3=0,0,Tithe). You could do the same with every one of your budget targets, or just with selected ones. 

 

Here's a good reference on Named Ranges: https://exceljet.net/glossary/named-range

There are experts here on this site who strongly recommend that virtually ALL formulas/functions make use of named ranges. That seems excessive to me, but named ranges are very helpful when you have to refer to things like, oh, tax rates (which can change) or assumed "cost of living" increases in a financial planning sheet, etc. The thing to look out for, to avoid, is hard-coding into your formulas variables that can and will change.