Tag Archive spreadsheet

15 Excel Formulas That Will Help You Solve Real Life Problems

excel-functions

A lot of parties view Microsoft Excel as a tool that’s simply handy 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 liberty formulas that solve problems.

Unlock the FREE “Essential Excel Formulas” chisel sheet now!

This will sign you up to our newsletter

Enter your EmailUnlockRead our privacy programme

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 strong implement that can help.

We picked out 15 formulae that are simple, potent, and help you solve complex issues.

Fiscal Formulas

Shopping for a new home and confused by all the mortgage lingo? Looking for a new vehicle and getting confused by the car loan expressions the salesperson restrains throwing at you?

Excel Formula Review

Have no fright. Before you take out a lend, do your search with Excel by your side!

1. PMT–Payment

Whenever you’re comparing any credit terms and want to quickly figure out your actual monthly payment granted different variations in calls, take advantage of the powerful( and simple) PMT formula.

PMT Excel Formula

Here’s what you need to use this formula 😛 TAGEND

The interest rate of the loan The term of the credit( how many payments ?) The starting principle of the credit Future ethic, if for some reason the loan will be considered paid off before it reaches zero( optional) Nature of credit — 0 if pays due at the end of each month, or 1 if they’re due at the start( optional)

Here’s a cool style to quickly compare a variety of lends to see what your fees will look like. Create an Excel sheet that rolls every potential loan and all available information about them. Then, create a “Payments” piece and use the PMT formula.

PMT Excel Formula for Interest

Just grab the lower right corner of the PMT cell you simply generated, and draw it down so it calculates the payment total for all the loan expressions be present in the membrane. The Excel autofill feature is one feature you will use a lot with these tricks.

Excel Table for Interest

Now you can compare monthly pays 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 apply the same period–in this case using monthly remittances necessitates fractioning the interest term by 12 months)

@rdube Egregious wrongdoings: 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 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 fund 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 loan Numeral of payments( or speculation term in months) The payment for each date( usually monthly) Current starting symmetry( optional) Category of loan — 0 if fees due at the end of each month, or 1 if they’re due at the start( optional)

So let’s compare several Cds using the terms that you know from the information the banks have given you. In the speciman 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 quality paid out. Here’s what the similarity looks like when you use the FV formula for every CD you’re considering.

Future Value Formula for Excel

Without a disbelieve, the higher interest CD over longer periods remunerates out something much. The only drawback is that you can’t touch any of your fund for three entire years, 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 spend using Excel, but sometimes the data you receives from banks is very disorganized.

Using logical formulae are a great way to recognize overspending.

Ideally, the bank either automatically categorizes your spend or you’ve set up your chronicle so that things are placed into spending categories. For precedent, any restaurants we go to get labeled with the DiningOut label.

Spending Categories in Excel

This induces it easy to use a logical formula to identify whenever we’ve gone out to eat and spent over $20.

To do this, precisely create a logical formula in a new column looking for any value where the category column is “DiningOut” and the busines tower is larger than – $20

Note: The comparison below establishes “ <“, 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 Lists

Lists are a big part of everyday life. If you’re managing a household5-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

Next

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

This formula looks at column A

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.

then they donated at least $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 will receive the special formatting. Don’t forget to click the Format … button and opt what kind of special formatting you want for these.

Also, a speedy tone. You’ll notice my range is defined as “$ E2 :$ E11” , not “$ E$ 2 :$ E $11 ”. When you select the series, it defaults to the former, which won’t work. You is in need of relative addressing as shown in the picture above. Then, your provisional 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 coincide 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 tower 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 piece is really easy.

10-14. Claim, LEFT, TEXT, and CONCATENATE

You can pull the year out of the verse in that column using the RIGHT formula.

CONCATENATE Formula for Excel

The formula above is telling Excel to make the textbook in piece D and remove the four references from the right side. The CONCATENATE formula pieces together those 4 digit, with the Payee text from editorial E.

Keep in spirit that if you do want to extract text from a appointment, you will need to convert it to text format( instead of date) exercising the “= TEXT( D2, ”mm/ dd/ yyyy”) ” formula. Then you can use the RIGHT formula to pull out the year.

What if your knowledge is on the left? Well, instead use the LEFT formula and you can pull text from left to right.

CONCATENATE truly comes in handy when “youve had” some verse from a cluster of different articles that you want to piece together into one long cord. There are also a few ways to separate text in Excel if you want to want to learn how to fully control strings.

Picking Random Names from a Hat 15. RANDBETWEEN

One last recreation formula is one you may use if you have to do something like pick some calls out of a hat for a Christmas party. Put that hat and those scraps of article away and instead pull out your laptop and propel Excel!

Using the formula RANDBETWEEN, you can have Excel haphazardly select a number between a range of numbers you specify.

RANDBETWEEN Formula for Excel

The two appreciates you need to use are the lowest and highest numbers, which should be at the ends of the reach of numbers you’ve applied to each person’s name.

Once you stumbled the Enter key, the formula will arbitrarily 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 formulae 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 “youve never” reputed Excel could do.

Image credit: Goodluz via Shutterstock.com

Read the full clause: 15 Excel Formulas That Will Help You Solve Real Life Problems

Read more: makeuseof.com