Forum Discussion
if And Formulas
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.
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.
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.
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!
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))
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"?
