if And Formulas

Question

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

mathetes · Answer

HSBell62&nbsp;&nbsp;A picture, the saying goes, is worth a thousand words.&nbsp;In the case of an Excel spreadsheet (one that already exists) a copy of the spreadsheet is probably worth, oh, ten thousand words.&nbsp;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.

hsbell62 · Answer

mathetes&nbsp;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.&nbsp;I started my IF AND formula in ROW 11 with Charter and want it to work from there on down and across.&nbsp;

mathetes · Answer

HSBell62&nbsp;&nbsp;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.&nbsp;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...).&nbsp;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?&nbsp;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"?

charla74 · Answer

HSBell62&nbsp;&nbsp;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.&nbsp; 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).&nbsp; Below formula (entered in B11) should help with this (I have updated for rows 11+ in the attached workbook):&nbsp;=IF(C10=0,0,IF($M11&lt;C10,$M11,C10))

mathetes · Answer

HSBell62&nbsp;&nbsp;Here's a variant on the solution offered by Charla74&nbsp;&nbsp;It uses the IFS function rather than nesting IF within IF. Otherwise the same. Perhaps easier to decipher.=IFS(C10=0,0,$M11&lt;=C10,$M11,$M11&gt;C10,C10)&nbsp;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]&nbsp;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.

Forum Discussion

if And Formulas

7 Replies

Resources