L
%Start 15 Excel Formulas That Will Help You Solve Real Life Problems
visibility
103 views
thumb_up
31 likes
D
A lot of people view 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.
thumb_up
2 likes
B
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.
thumb_up
35 likes
I
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.
thumb_up
5 likes
E
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.
thumb_up
33 likes
S
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.
thumb_up
49 likes
O
The 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) This is why our readers are so great.
thumb_up
7 likes
L
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.
thumb_up
29 likes
A
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.
thumb_up
34 likes
M
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.
thumb_up
24 likes
J
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.
thumb_up
35 likes
M
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.
thumb_up
36 likes
S
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.
thumb_up
47 likes
I
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.
thumb_up
13 likes
D
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.
thumb_up
37 likes
N
The AND statement checks whether the category is "DiningOut" and the transaction is greater than $20. There you have it!
thumb_up
6 likes
G
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.
thumb_up
14 likes
R
, 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.
thumb_up
18 likes
L
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.
thumb_up
28 likes
J
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.
thumb_up
32 likes
T
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.
thumb_up
18 likes
D
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.
thumb_up
16 likes
J
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.
thumb_up
29 likes
D
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.
thumb_up
15 likes
M
This formula is one of many 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.
thumb_up
13 likes
S
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.
thumb_up
42 likes
C
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.
thumb_up
13 likes
D
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...
thumb_up
12 likes
S
button and choose what kind of special formatting you want for these. Also, a quick note.
thumb_up
15 likes
I
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.
thumb_up
32 likes
J
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.
thumb_up
45 likes
L
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.
thumb_up
19 likes
A
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.
thumb_up
15 likes
C
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.
thumb_up
20 likes
J
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.
thumb_up
29 likes
N
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. You can find out more in . There are also a if you want to want to learn how to fully manipulate strings.
thumb_up
38 likes
N
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.
thumb_up
24 likes
A
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.
thumb_up
30 likes
J
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.
thumb_up
24 likes
L
Anyone can benefit from the . Learn these formulas and you can start solving real-life problems in Excel. Don't stop learning Excel.
thumb_up
48 likes
E
There is a that you can learn to use, you might find some neat little tricks you never thought Excel could do. Image credit:
thumb_up
46 likes
Similar Discussions
-
My heart hurt when I yelled Tyler1 talks about his heart condition and gives a health update
-
Neighbor Grandpa Sponge Scary APK Download for Android
-
Virtual Doctor Hospital Games APK Download for Android
-
Boxe Sous forme d hologramme Manny Pacquiao annonce son retour sur le ring
-
Turista se desnuda en las escaleras de una catedral italiana
-
How to Connect Sonos Beam to TV
-
How the Xbox Network on Nintendo Switch Works
-
Compare BMW Z4 Coupe vs BMW Z4 M Coupe CarBuzz
-
2021 Mini John Cooper Works GP Safety amp Reliability Ratings Warranty Crash Test Ratings Safety Features CarBuzz
-
There s Now A Much Cheaper Way To Own A Porsche 911 R CarBuzz
-
Photo Printer Paper Walmart
-
Black Xfinity Logo
-
How old is PinupPixie Influencer goes viral for revealing 18 year old son
-
Epic Games secures $1bn funding including $200m from Sony
-
Nvidia RTX 3090 graphics card available now at these retailers
-
Apex Legends wraps up third season with Grand Soirée Arcade event next week
-
iOS 16 Live Text mdash the biggest changes coming to your iPhone Tom s Guide
-
Micro white eczane fiyatı
-
Aile hekimi sağlık raporu ehliyet
-
Amedspor altyapı seçmeleri 2019
-
Almejas picantes de Florida Cesar Zapata
-
No 10 Duke sweeps No 19 NC State in ACC opener
-
Apollon vs Vaduz Prediction and Betting Tips November 3 2022
-
Who was eliminated on Survivor Season 43 Episode 6? The Baka tribe fallout merge and more explored
-
Hockey India League 2017 Live Score Dabang Mumbai vs Kalinga Lancers 3 4 HIL 2017 Live commentary and updates
-
J J Watt s kind gesture earns fan praise
-
Who won yesterday s IPL match 2022 RCB vs GT Match 67?
-
25 Nintendo Pictures From The 90s That Show Why Gamers Have Trust Issues
-
This New Anime Is About A Rite Aid For Catgirls
-
13 Sentinels Aegis Rim On Switch Will Feature Brand New Bonus Content