15 Excel Formulas That Will Help You Solve Real Life Problems
A lot of parties position Microsoft Excel as a tool that’s only beneficial 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.
Unlock the FREE “Essential Excel Formulas” mislead sheet now!
This will ratify you up to our newsletter
Enter your EmailUnlockRead our privacy programme
Whether you’re shopping for a new auto 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 potent implement that can help.
We picked out 15 formulae that are simple, potent, and help you solve complex issues.
Busines Formulas
Shopping for a brand-new home and confused by all the mortgage lingo? Looking for a brand-new car and get confused by the car loan calls the salesperson remains throwing at you?
Have no anxiety. Before you take out a credit, do your research with Excel by your place!
1. PMT–Payment
Whenever you’re comparing any lend terms and want to quickly figure out your actual monthly pay dedicated different variations in terms, take advantage of the potent( and simple) PMT formula.
Here’s what you need to use this formula 😛 TAGEND
The interest rate of the lend The term of the credit( how many payments ?) The starting principle of the lend Future cost, if for some reason the loan will be considered paid off before it reaches zero( optional) Category of credit — 0 if remittances due at the end of each month, ose 1 if they’re due at the start( optional)
Here’s a cool space to quickly compare a variety of lends to see what your pays will look like. Create an Excel sheet that rolls every potential loan and all available information about them. Then, create a “Payments” article and use the PMT formula.
Just grab the lower right corner of the PMT cell you only made, and drag it down it is therefore calculates the remittance total for all the loan terms be present 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 said that for PMT and FV formulas, you’ve got to be very careful about employ the same period–in this case working monthly remittances involves fractioning the interest term by 12 months)
@rdube Egregious mistakes: formula PMT& FV calculates percentage per month , not per year. Interest rates off by 12 x.https :// t.co/ wrC5eXFzNw
— Mark Jones (@ redtexture) Nëntor 26, 2015
This is why our books 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 coin 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 😛 TAGEND
The interest rates of the credit Digit of payments( or asset period in months) The remittance for each interval( often monthly) Current starting match( optional) Kind of lend — 0 if pays due at the end of each month, ose 1 if they’re due at the beginning( optional)
So let’s compare various CDs working the terms that you know from the information the banks have given you. In the lesson below, let’s say you have a $ 20,000 inheritance to invest in a CD.
Interest rates are again represented in decimal format( make the interest rate the bank gave you and partition by 100 ). Payments are zero because CD’s are typically based on a starting value and a future importance paid out. Here’s what the comparing looks like when you use the FV formula for every CD you’re considering.
Without a skepticism, the higher interest CD over longer periods bribes out something much. The only drawback is that you can’t touch any of your coin for three entire times, but that’s the nature of investing!
3-4. Logical Formulas–IF and AND
Most banks these days give you the capacity needed 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 receives from banks is very disorganized.
Using logical formulae are a great way to spot overspending.
Ideally, the bank either automatically categorizes your spend or you’ve set up your history so that things are placed into spending lists. Për shembull, any eateries we go to get labeled with the DiningOut label.
This offsets it easy to use a logical formula to identify whenever we’ve gone out to eat and wasted over $20.
To do this, only create a logical formula in a brand-new article looking for any value where the category column is “DiningOut” and the transaction line are greater than – $20
Note: The similarity below displays “ <“, less than, because the values in column C are all negative.
Here’s what that looks like:
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 transactionsMaking Sense of Listsas 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
Next
This gives you the count of every place in Column E where there’s a 1 rather than a blank.
but it does require two steps.
6-8. SUMIFCOUNTIF
and if the cell within the range matches the criteria in quotes
It’s much faster than using two columns
The SUMIF and AVERAGEIF formulas work the very same way9. 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
Now you can add additional formatting to make it easier on the eyes.
select the Home tab in the menu
Set the range under Format values where this formula is true: to the column/range where all your LEN formula outputs are displayed.
NNP 3”, then anything over $1,000 are able to obtain the special formatting. Don’t forget to click the Format … button and pick what kind of special formatting you require for these.
Also, a rapid indicate. You’ll notice my scope is defined as “$ E2 :$ E11” , not “$ E$ 2 :$ E $11 ". When you have chosen the scope, it defaults to the onetime, which won’t work. You is in need of 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 parallel what you need it to be.
Për shembull, 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 editorial of your own with your own that’s prefaced by the year and includes the Payee information( for your own sorting intents ), extracting pieces of information from a editorial is really easy.
10-14. Freedom, LEFT, TEXT, and CONCATENATE
You can pull the year out of the verse in that column consuming the RIGHT formula.
The formula above is telling Excel to take the textbook in article D and extract the four people from the right side. The CONCATENATE formula pieces together those four digits, with the Payee text from article E.
Keep in spirit that if you do want to extract text from a year, you will need to convert it to text format( instead of date) exploiting the “= TEXT( D2, ”mm/ dd/ yyyy”) ” formula. Then you can use the RIGHT formula to pull out the year.
What if your message is on the left? mirë, instead use the LEFT formula and you can pull text from left to right.
CONCATENATE genuinely comes in handy when you have some textbook from a cluster of different articles that you want to piece together into one long cord. There is still a few ways to separate textbook in Excel if you want to want to learn how to fully control strings.
Picking Random Names from a Hat 15. RANDBETWEEN
One last enjoyable formula is one you may use if you have to do something like pick some specifies out of a hat for a Christmas party. Put that hat and those scraps of paper apart and instead pull out your laptop and launch Excel!
Using the formula RANDBETWEEN, you are able to have Excel randomly select a number between a range of numbers you specify.
The two importances you need to use are the lowest and highest digits, which should be at the ends of the wander of numbers you’ve applied to each person’s name.
Once you smacked the Enter key, the formula will haphazardly 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, picking 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 discover Excel. There is a long list of Excel formulas and functions that you can learn to use, you might find some neat little tricks “youve never” meditated Excel could do.
Image credit: Goodluz via Shutterstock.com
Read the full essay: 15 Excel Formulas That Will Help You Solve Real Life Problems
Read more: makeuseof.com