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