15 Excel Formulas That Will Help You Solve Real Life Problems

Posted On Feb 13, 2020 By admin With Comments Off

excel-functions

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?

Excel Formula Review

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.

PMT Excel 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, or 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.

PMT Excel Formula for Interest

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.

Excel Table for Interest

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) November 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, or 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.

FV Excel Formula

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.

Future Value Formula for Excel

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. For example, any eateries we go to get labeled with the DiningOut label.

Spending Categories in Excel

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:

IF AND Formula for Excel

or FALSE if it isn’t. The AND statement checks whether the category is “DiningOut” and the transaction is greater than $20.

Excel Table for Formula

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.

COUNT and COUNTIF Formula for Excel

We want to see how many times a person’s name shows up on the list. To do this

IF Formula for Excel

COUNT Formula for Excel

This gives you the count of every place in Column E where there’s a 1 rather than a blank.

Excel Table Result CountIf Formula

but it does require two steps.

6-8. SUMIFCOUNTIF

COUNTIF Formula for Excel

and if the cell within the range matches the criteria in quotes

Excel Table for CountIF Formula

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

LEN Formula for Excel

Now you can add additional formatting to make it easier on the eyes.

select the Home tab in the menu

Excel Conditional Formatting

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.

For example, let’s say that in the exported data from your bank and you’re given the date in the standard format.

Bank Information Excel

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.

CONCATENATE Formula for Excel

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? Well, 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.

RANDBETWEEN Formula for Excel

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.

RANDBETWEEN Formula for Excel

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