I would like to create a formula to calculate a payment (PMT) amount based on the current balance (CBAL).
If CBAL is <$300, PMT=CBAL;
if CBAL is >=$300 but <$2000, PMT=$300;
if CBAL is >=$2000, PMT=$500
Also, what training materials or instruction recommend for someone who uses excel a lot in a very superficial basic way to learn more intermediate to advanced skills. I’d like to be able to understand how to create formulas and lookup tables and the like.
I’m a FileMaker developer who only dabbles in Excel, but I’m pretty sure you have to nest the IF statements, so that their structure is akin to
If(x, y; (if(w; z; If(v;a)))
… so to create them, you insert a function right in the place where the “else” result would normally be and put in yet another If there.
That’s also how FileMaker used to work way back when, although they gave us Case () which is more elegant and more akin to what you wrote —
Case (x, y; w, z; v, a)
I haven’t tried it myself, but it looks like the latest version of Excel has a function IFS that lets you specify multiple conditions without nesting IF statements.
AHunter3 correctly describes nested IF’s and mentions a Case statement - Excel has the Switch function which is the same thing. This is described quite well here - Link
Thudlow - the IFS looks like the same thing?
Yes, IFS is tidier. This works:
=IFS(CBAL<300,CBAL,CBAL>=2000,500,TRUE,300)
The arguments are a list of conditions paired with the value to return if the condition is true. The function returns the value associated with the first true condition encountered in the list.
From your link, it looks like SWITCH only works if your conditions are that one thing is equal to something else.
Ah yes. I sometimes use VLOOKUP for lookups like this - the advantage is that you can easily change your parameters in the lookup table - and the cell functions don’t change.
My inclination in these circumstances is to not have the thresholds hard coded into the formulas. If events came that the thresholds changed but in error not all of the formulas were changed eg there was a filter on when changed then is is problematic to identify that there is an error.
So either use VLOOKUP or use cell references in the IF statement would be my suggestion.
In theory, I suppose nesting is the right way to go. But when a formula gets too long, I find it to be very easy for a typing error to mess it up. Much safer to break it up into several intermediate steps placed in several columns, and then one column that gives the result you’re looking for.
In Excel, a range that’s bounded on both ends can be checked using an AND() statement:
AND(logical test 1, logical test 2) evaluates to true if both logical tests are true.
So: AND(CBAL>=300, CBAL <2000) is what you would use to check for this range.
The IFS() statement is new to me, and looks like a cleaner way (than IF() ) to code things. If you’re doing it with old-school IF() statements, you end up nesting them, which gets convoluted after more than two or three levels. The basic format of the IF() statement is this:
IF(logical test, value if true, value if false)
To nest them, you just put an entire other IF() statement where “value if false” is:
IF(logical test1, value1 if true, IF(logical test2, value if true, value if false))
Your three tests cover the entire range of possible values for CBAL, so if the tests for the first two ranges (CBAL<300, or 300<=CBAL<2000) both return false, we don’t have to evaluate a third statement: we know that CBAL must be >=2000. So there’s only two levels of IF() statements required to sort this out:
PMT = IF(CBAL<300, CBAL, IF(AND(CBAL>=300, CBAL <2000), 300, 500))
You could also just check for the open-ended upper range, and skip checking for the middle range, which lets you eliminate the AND() statement:
PMT = IF(CBAL<300, CBAL, IF(CBAL >= 2000, 500, 300))
Seconded. don’t bury decision values out of sight in cell formulas, put them all together in a table (with labels) and reference them. Easier to see what values are being used, and change them, especially of any of those values are being used in multiple places in the sheet.
For starters, it’s useful just to be aware of what basic functions are available in Excel itself. Go to the “Formulas” tab, click any one of the formula categories, and scroll all the way dow n to where it says “Insert Function…” Click on that, and you can get an “Insert Function” dialog that makes it easy to browse through all of the various functions and see a short text description of what each function does. Sort of like inheriting a toolbox, and digging through it drawer by drawer to see what wrenches, screwdrivers, and hammers you have at your disposal.
For more complex schemes that make use of those functions, there’s a very good chance someone else has already done what you’re trying to do (and shared their methods) online. Search the web using a basic text description of whatever you’re trying to do (make sure you include “in Excel” in your search terms), and you’ll probably find something close to what you want.
Blockquote
You could also just check for the open-ended upper range, and skip checking for the middle range, which lets you eliminate the AND() statement:
PMT = IF(CBAL<300, CBAL, IF(CBAL >= 2000, 500, 300))
This worked perfectly and was easy to understand. Thank you!
I also tried the elegant IFS function, but it looks like that’s a more recent excel function as it doesn’t appear to be included in my 2012 version of Excel that was bundled with the MS Office Professional Plus 2016 product.
I have reviewed the functions descriptions but I was hoping for more of a tutorial. I have been using Excel to track our finances and have been adapting and customizing the spreadsheet based on what I want to see and how. I know my spreadsheet is kludgy at best and doesn’t do a lot of the work for me which means I’ve been spending far more time messing with than I need to. The frustrating thing is that I know there are ways to make it better but I don’t have the skills to do so. I think improving my Excel skills could benefit me in more ways that I realize.
As you’re reviewing the function descriptions in the “Insert Function” dialog, click on the “Help on this function” link at bottom left; you’ll get a more detailed description, authored by Microsoft, of how to use that function. Example, the COUNTIF() function. Microsoft’s description looks like this. If you’re still not clear on something, you can just google excel countif; the search results will include lots of third-party sites with their own explanations, examples, and tutorials. ExcelJet is a good one, but there are plenty of others if their style doesn’t suit you.
As far as kludginess goes, there are a lot of style and best-practices guides out there that can advise you on how to build spreadsheets that are easy to understand/use/modify. One example is the kind of thing penultima_thule mentioned, i.e. putting all of your parameters of interest in one place where you can see and modify them with ease (item #10 in this best practices guide). Suppose for example that you you want to project how your nest egg will grow over the next twenty years as you make annual deposits and reap annual returns. So you make a column of twenty cells, with each row adding a deposit and a percentage onto the balance from the previous row/year. Instead of putting specific deposit/return values in each of those cells, you write your deposit and return values once in a convenient block of cells, and then just reference those values in each year’s calculation. Then you can conveniently adjust your deposit/return values and see how it affects your nest egg at the end of twenty years.
Do you know about very basic things like dragging the corner of a cell to propagate a formula to an entire row/column, and using “$” to lock down row/column references in your formulas? Tracing dependents/precedents to see which cells reference (or are referenced by) a given cell?
Yes on the first two, but no on the last. I have LOTS to learn but I can “use” Excel to do what I want (mostly), if not in the most helpful or elegant way. My understanding of Excel is about as basic as multiple tabs in Word instead of setting tab stops or using templates.
This is going to help me immensely. I get these concepts but I feel like I need to improve my skills and understanding of all of the things Excel is capable of in order to put them into practice on my spreadsheet. If I had a better understanding of advanced functions and tools I could really make my spreadsheet work better.
I have looked at budgeting spreadsheets but haven’t found one that presents the data in the way that I want to see it. Mine does for the most part, but isn’t very efficient for continuing use. I basically have to rebuild it every month, which is tedious and stupid. But it helps me visualize where all the pieces are at any one time, which is important since we’ve been operating on an all in/all out situation while we’re aggressively saving and paying off debts and not letting any money just sit around unallocated. It’s more than just budgeting but shows me all pieces of the pie in one place. Maybe I haven’t found what I’m looking for because this is a dumb way to do it, but for the time being it’s working for me.
[bolding mine] I feel like if I learn this it will be profoundly helpful. By cell references, do you mean the actual cell designation (i.e., B5 or $B$5) or do you mean actually naming the cells?
As suggested by @Machine_Elf, in most workbooks I generate which I have a separate tab named vlookup where all the vlookup ranges are held and ideally each is a named range including some blank cells for additional values without needing to insert rows and affect all the ranges.
Similarly with standard variables (ie users don’t play what if with) date ranges, exchange rates, QA thresholds etc go in a block at the top of the vlookup tab. If I am building the workbook for somebody else’s use or isn’t a once off analysis I usually colour the cells, name them and include descriptive text or comment box.
For workbooks I maintain myself I often aren’t so particular and use summat like =‘vlookup’:$B$15
Whether or not you name the cell is not the issue. If your condition is based on CBAL being less than a threshold of 300, put the 300 in a cell and reference that cell in your IF expression, don’t “hard code” the number 300 into your IF expression. Then if you want to change the 300 threshold to 350, you just have to change the value in that cell, you don’t have to rewrite the entire IF expression.
I teach a college-level Office class and we spend all of three weeks on Excel to cover just the basics. I make sure students know a basic =IF statement and how to use =VLOOKUP, plus use of absolute cell references ($ signs) and multiple-worksheet usage (! symbols) with named ranges/cells.
My personal laptop is stuck on Office 2013, so I don’t use =IFS and =SWITCH, but they make total sense to me (coming from a programmer’s view); they would solve lots of problems that arise with nested =IF statements. I’d have my students use them, but they only showed up in 2019 and I can’t guarantee that my students have the latest versions installed.
Try to look at what you’re rebuilding every month and see if there’s a way to make it so it’s much more reusable (there probably is). Example, if you’re making conscious decisions each month about how to add expenses together in the spreadsheet, or where to enter them in the spreadsheet, but those decisions are based on information that is (or could be added) in that spreadsheet, then make the spreadsheet automatically use those decision criteria to figure out how to add expenses together. The logical operators (IF, OR, AND, etc.) are at the root of this, but you can do much more than just compare numerical values. You can make decisions based on whether a cell is empty or not (ISBLANK), what type of data is in a cell (ISNUMBER, ISTEXT, etc), whether a cell or expression evaluates to an error (ISERROR, ERROR.TYPE, ISNA), and so on. Your IF() statements don’t even have to put something in a cell, either:
IF(logical_test, [value_if_true], “”)
if logical_test is false, this leaves the cell blank. Nice for tidy-looking worksheets, or when you don’t want an actual zero value to be factored into a subsequent averaging calculation.
The more you can familiarize yourself with the available functions - especially by testing them out yourself as you first encounter them in the list of available functions - the more you can imagine ways they can be used together to build a kind of “calculating machine”. Formal instruction can help, but it’s not required. I’ve been using Excel for 20+ years without formal instruction, and just kind of learned as I went. There’s a lot of functionality there, and while I’m more capable than most of my colleagues, there are others still who can absolutely run circles around me.