A lot of people view Microsoft Excel as a tool that’s only useful in business. Truth is, there are a lot of ways it can benefit you at home as well. The key to finding uses of Excel in daily life is picking the right formulas that solve problems.
Whether you’re shopping for a new car loan, want to figure out which mutual fund investment is best for you, or if you’re just trying to make sense out of your bank account, Excel is a powerful tool that can help.
We picked out 15 formulas that are simple, powerful, and help you solve complex issues.
Financial Formulas
Shopping for a new home and confused by all the mortgage lingo? Looking for a new car and getting confused by the car loan terms the salesperson keeps throwing at you?
Have no fear. Before you take out a loan, do your research with Excel by your side!
1. PMT—Payment
Whenever you’re comparing any loan terms and want to quickly figure out your actual monthly payment given different variations in terms, take advantage of the powerful (and simple) PMT formula.
Here’s what you need to use this formula:
- The interest rate of the loan
- The term of the loan (how many payments?)
- The starting principle of the loan
- Future value, if for some reason the loan will be considered paid off before it reaches zero (optional)
- Type of loan—0 if payments due at the end of each month, or 1 if they’re due at the beginning (optional)
Here’s a cool way to quickly compare a variety of loans to see what your payments will look like. Create an Excel sheet that lists every potential loan and all available information about them. Then, create a “Payments” column and use the PMT formula.
Just grab the lower right corner of the PMT cell you just created, and drag it down so it calculates the payment total for all the loan terms listed in the sheet. The Excel autofill feature is one feature you will use a lot with these tricks.
Now you can compare monthly payments for different kinds of loans.
(A very big thank you to Mark Jones (@redtexture on Twitter) who pointed out that for PMT and FV formulas, you’ve got to be very careful about using the same period—in this case using monthly payments requires dividing the interest term by 12 months)
@rdube
Egregious errors: formula PMT & FV calculates percentage per month, not per year. Interest rate off by 12x.https://t.co/wrC5eXFzNw— Mark Jones (@redtexture) November 26, 2015
This is why our readers are so great. Thanks for helping with this fix Mark!
2. FV—Future Value
The next formula comes in handy when you are looking to invest some money into something like a Certificate of Deposit (CD), and you want to know what it will be worth at the end of the term.
Here’s what you need to know to use the FV formula:
- The interest rate of the loan
- Number of payments (or investment term in months)
- The payment for each period (usually monthly)
- Current starting balance (optional)
- Type of loan—0 if payments due at the end of each month, or 1 if they’re due at the beginning (optional)
So let’s compare several CDs using the terms that you know from the information the banks have given you. In the example below, let’s say you have a $20,000 inheritance to invest in a CD.
Interest rates are again represented in decimal format (take the interest rate the bank gave you and divide by 100). Payments are zero because CD’s are typically based on a starting value and a future value paid out. Here’s what the comparison looks like when you use the FV formula for every CD you’re considering.
Without a doubt, the higher interest CD over a longer period of time pays out much more. The only drawback is that you can’t touch any of your money for three whole years, but that’s the nature of investing!
3-4. Logical Formulas—IF and AND
Most banks these days give you the ability to download nearly a year’s worth of bank transactions to a format like CSV. This is a perfect format to analyze your spending using Excel, but sometimes the data you receive from banks is very disorganized.
Using logical formulas are a great way to spot overspending.
Ideally, the bank either automatically categorizes your spending or you’ve set up your account so that things are placed into spending categories. For example, any restaurants we go to get labeled with the DiningOut label.
This makes it easy to use a logical formula to identify whenever we’ve gone out to eat and spent over $20.
To do this, just create a logical formula in a new column looking for any value where the category column is “DiningOut” and the transaction column is larger than -$20
Note: The comparison below shows “<“, less than, because the values in column C are all negative.
Here’s what that looks like:
Using IF and AND together in one formula looks tricky, but it’s actually quite simple. The IF statement will output the dollar amount (C2) if the AND statement is true, or FALSE if it isn’t. The AND statement checks whether the category is “DiningOut” and the transaction is greater than $20.
There you have it! Without having to manually sift through all those transactions, you now know exactly those times when you’ve overspent in a certain category.
Making Sense of Lists
Lists are a big part of everyday life. If you’re managing a household, you’re using lists constantly. Excel has some pretty powerful tools for being productive with checklists, as well as other kinds of list formats.
5-6. COUNT and COUNTIF
Excel can help you quickly organize and sort values is a list. Let’s take the PTC example. Here’s a list of donations from community members.
We want to see how many times a person’s name shows up on the list. To do this, you can combine the COUNT formula with an IF formula. First, create a column to check if the person is Michelle or not. The formula will use an IF statement to fill the cell with a “1” if this is true.
Next, create another column that counts how many times you’ve found Michelle Johnson on the list.
This gives you the count of every place in Column E where there’s a 1 rather than a blank.
So, this is the simplest way to do this kind of thing, but it does require two steps.
6-8. SUMIF, COUNTIF, AVERAGEIF
If you don’t mind using a slightly more advanced formula, you might consider using one of the many combined “IF” formulas like SUMIF, COUNTIF, or AVERAGEIF. These allow you to perform the formula (COUNT, SUM or AVERAGE) if the logical condition is true. Here’s how it works using the above example.
This formula looks at column A, which contains all the donor names, and if the cell within the range matches the criteria in quotes, then it counts up by one. This gives you a count of all the times the donor name equals “Michelle Johnson” in a single step.
It’s much faster than using two columns, but is a little complex – so use the approach that works best for your situation.
The SUMIF and AVERAGEIF formulas work the very same way, just with different mathematical results. Using SUMIF in this example would give you the total donation dollars for Michelle Johnson if you use it instead.
9. LEN
Another formula that you can use creatively sometimes is the LEN formula. This formula is one of many Excel text formulas that tells you how many characters are in a string of text.
One interesting way to use this in the example above would be to highlight donors who donated over $1,000 by counting the number of digits in the donation column. If the length of the number is 4 or greater, then they donated at least $1,000.
Now you can add additional formatting to make it easier on the eyes.
To do this, you need to highlight all the cells in the Donation column, select the Home tab in the menu, and click on Conditional Formatting in the toolbar. Then select Use a formula to determine which cells to format.
Set the range under Format values where this formula is true: to the column/range where all your LEN formula outputs are displayed.
In this example, if you make the condition “>3”, then anything over $1,000 will receive the special formatting. Don’t forget to click the Format… button and choose what kind of special formatting you want for these.
Also, a quick note. You’ll notice my range is defined as “$E2:$E11”, not “$E$2:$E$11”. When you select the range, it defaults to the former, which won’t work. You need to use relative addressing as shown in the picture above. Then, your conditional formatting will work based on the condition of the second range.
Organizing Bank and Financial Downloads
Sometimes, when you download information from businesses—whether it’s your bank, or your health insurance company, the format of the incoming data doesn’t always match what you need it to be.
For example, let’s say that in the exported data from your bank and you’re given the date in the standard format.
If you want to add a new column of your own with your own that’s prefaced by the year and includes the Payee information (for your own sorting purposes), extracting pieces of information from a column is really easy.
10-14. RIGHT, LEFT, TEXT, and CONCATENATE
You can pull the year out of the text in that column using the RIGHT formula.
The formula above is telling Excel to take the text in column D and extract the four characters from the right side. The CONCATENATE formula pieces together those four digits, with the Payee text from column E.
Keep in mind that if you do want to extract text from a date, you will need to convert it to text format (instead of date) using the “=TEXT(D2,”mm/dd/yyyy”)” formula. Then you can use the RIGHT formula to pull out the year.
What if your information is on the left? Well, instead use the LEFT formula and you can pull text from left to right.
CONCATENATE really comes in handy when you have some text from a bunch of different columns that you want to piece together into one long string. There are also a few ways to separate text in Excel if you want to want to learn how to fully manipulate strings.
Picking Random Names from a Hat
15. RANDBETWEEN
One last fun formula is one you may use if you have to do something like pick some names out of a hat for a Christmas party. Put that hat and those scraps of paper away and instead pull out your laptop and launch Excel!
Using the formula RANDBETWEEN, you can have Excel randomly select a number between a range of numbers you specify.
The two values you need to use are the lowest and highest numbers, which should be at the ends of the range of numbers you’ve applied to each person’s name.
Once you hit the Enter key, the formula will randomly select one of the numbers within the range.
It’s about as random and tamper-proof as you can possibly get. So instead of picking a number from a hat, pick a number from Excel instead!
Using Excel for Everyday Problems
As you can see, Excel isn’t just for data-analysis gurus and business professionals. Anyone can benefit from the many formulas that you’ll find tucked away in Excel. Learn these formulas and you can start solving real-life problems in Excel.
Don’t stop learning Excel. There is a long list of Excel formulas and functions that you can learn to use, you might find some neat little tricks you never thought Excel could do.
Image credit: Goodluz via Shutterstock.com
Read the full article: 15 Excel Formulas That Will Help You Solve Real Life Problems
Read Full Article
No comments:
Post a Comment