Pinterest Stumbleupon Whatsapp
Ads by Google

A lot of people view Excel as a tool that’s only good for business applications 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things I have always believed that Excel is one of the most powerful software tools out there. It's not just the fact that it's spreadsheet software. No, Microsoft Excel 2013 simply has an awesome collection of... Read More , but when you see the simple and useful formulas below, you’ll see that Excel can also help you solve real life problems at home.

Whether you’re shopping for a new car loan The 6 Best Online Tools For Car Buyers The 6 Best Online Tools For Car Buyers Buying a new car is a big decision. The Internet is the best way to research new cars – and also the best was to calculate the costs associated with them. Let’s have a look... Read More , 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.

The 15 formulas below were carefully selected for both simplicity and for how powerful they are with helping regular people figure out complex issues Doing Your Taxes? 5 Excel Formulas You Must Know Doing Your Taxes? 5 Excel Formulas You Must Know It's two days before your taxes are due and you don't want to pay another late filing fee. This is the time to leverage the power of Excel to get everything in order. Read More . If you’re a teacher trying to sort out student grades My GradeBook For Students Grades & Homework - The Perfect Companion For School [iOS, Paid Apps Free] My GradeBook For Students Grades & Homework - The Perfect Companion For School [iOS, Paid Apps Free] I remember when I was a student, the suspense of waiting to find out what my grades were on report card day was always terrible. I did well in school, but for some reason, the... Read More , a parent trying to get a handle on your household spending, or a young couple shopping for your first home loan, the formulas below can help.

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 sales person keeps throwing at you?

excel-formula1

Have no fear. Before you start shopping, do your research with Excel by your side!

Ads by Google

PMT – Payment

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

excel-formula2

Here’s what you need to use this formula:

  • the percentage 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; just 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.

excel-formula23

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 of the loan terms Personal Loans Online -- How Peer-to-Peer Lending Is Changing the Game Personal Loans Online -- How Peer-to-Peer Lending Is Changing the Game A number of services on the market right now allow lenders to provide credit, and for borrowers to gain access to funds without dealing with a bank. They're shaking up the finance game. Read More listed in the sheet.

excel-formula24

As you can see, when you can compare loan terms side-by-side, some stark realities show forth. For example, for a car loan of $19,500, if you take the time to show around and get a loan that’s just a few interest points lower, it could save you a lot of money each month, and thousands of dollars over the life of the loan.

(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 – thanks for helping with this fix Mark!

FV – Future Value

The next formula comes in handy when you are looking to invest some money into something like a CD (Certificate of Deposit), and you want to know what the CD will be worth at the end of the term.

Here’s what you need to know to use the FV formula:

So let’s compare several bank 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.

excel-formula25

As you can see, interest rates are again represented in decimal format (take the interest rate the bank gave you and divide by 100), and 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.

excel-formula26

Without a doubt, the higher interest CD over a longer period of time pays out handsomely more. The only drawback of course is that you can’t touch any of your money for three whole years. For a lot of people, that’s just a little too long, but that’s the nature of investing!

Logical Functions (IF or 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 functions is 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.

excel-formula7

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

To do this, just create a logical function in a new column, looking for any value where the category column is “DiningOut” and the transaction column is larger than -$20 (the comparison below shows “<“, less than, because the values in column C are all negative. Here’s what that looks like:

excel-formula8

Using IF and AND together in one formula looks tricky, but if you think about it, it makes perfect sense. 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.

excel-formula9

There you have it! Without having to manually sift through all of those transactions, you now know exactly those times when you’ve overspent in a certain category. As usual, Excel formulas turn out to be a massive time-saver.

Making Sense of Lists

Lists are a big part of everyday life. If you’re managing a household, you’re using lists constantly.

Maybe you’re on the Parent-Teacher Committee and you’ve been sent a long list of community donations Crowdrise - Armchair Fundraising for Charities & Events Crowdrise - Armchair Fundraising for Charities & Events Read More . It’s up to you to figure out who in the community donated to a specific school project, and how much. Or maybe you run a family business and you do payroll for the half-dozen employees you employ, and have to summarize their individual expense charges Manage Your Budget & Expenses Using Google Budgeting Tools Manage Your Budget & Expenses Using Google Budgeting Tools Read More .

COUNT and COUNTIF

Whatever the list is, Excel can help you quickly organize and sort values.  Let’s take the PTC example. Here’s a list of donations from community members.

excel-formula10

If you want to see how many times a person’s name shows up in the list, you can combine the COUNT formula with an IF formula Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Counting and adding formulas may appear mundane compared to more advanced Excel formulas. But they can help you save a lot of time when you need to collect information about the data in your spreadsheet. Read More . So, first create a column to check if the person is Michelle or not. If it is, make the cell a 1. If it isn’t, make it a space ” “.

excel-formula11

Next, create another column that counts how many times you’ve found Michelle Johnson in the list.

excel-formula12

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

excel-formula13

So, this is the simplest way to do this kind of thing, but it does require two steps.

If you don’t mind using a slightly more advanced function, you might consider using one of the many combined “IF” functions like SUMIF, COUNTIF, or AVERAGEIF. These allow you to only do the function (COUNT, SUM or AVERAGE) if the logical condition is true. Here’s how it works using the above example.

excel-formula14

This formula looks at column A, which contains all of 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 of the times the donor name equals “Michelle Johnson” in a single step.

excel-formula15

It’s much faster than using two columns, but for some people it may be a little too complex – so use the approach that works best for your situation.

LEN Function

Another function that you can use creatively sometimes is the LEN function. This function tells you how many characters are in a string of text Saving Time with Text Operations in Excel Saving Time with Text Operations in Excel Excel can do magic with numbers and it can handle characters equally well. This manual demonstrates how to analyze, convert, replace, and edit text within spreadsheets. These basics will allow you to perform complex transformations. Read More .

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.

excel-formula16

Then, if you want to, you can even format another column based on the value in column E; the result of the LEN function.

To do this, you need to highlight all of 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.

excel-formula17

Set the range under Format values where this formula is true: to the column/range where all of your LEN function 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”. For some reason 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, your health insurance company CakeHealth: An Effective Way To Manage & Track Health Care Spendings CakeHealth: An Effective Way To Manage & Track Health Care Spendings Read More , or anywhere else you do business, 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, you’re given the date in the standard format.

excel-formula18

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.

RIGHT, LEFT, TEXT, and CONCATENATE

You can pull the year out of the text in that column using the RIGHT function.

excel-formula19

The function above is telling Excel to take the text in column D and extract the four characters from the right side. The CONCATENATE function pieces together those four digits, with the Payee text from the next column.

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”)” function. Then you can use the RIGHT function to pull out the year.

excel-formula20

CONCATENATE really comes in handy when you have text from a bunch of different columns that you want to piece together into one long string. For example, you could concatenate a person’s first name, last name, street address, town, and state into a field that contains the person’s entire address in one single column.

Picking Random Names from a Hat

One last fun function is one you may use if you have to do something like pick some names out of a hat How to Get a Free Raffle Ticket Template for Microsoft Word How to Get a Free Raffle Ticket Template for Microsoft Word Fundraising is a lot of work. Let us take some of it off of your shoulders and show you how to find templates and customize free raffle tickets. All you need is a word processor. Read More for a Christmas party. Put that hat and those scraps of paper away and instead pull out your laptop and launch Excel!

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

excel-formula21

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 function will randomly select one of the numbers within the range.

excel-formula22

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 How to Become a Data Scientist How to Become a Data Scientist Data science has gone from a newly coined term in 2007 to being one of the most sought-after disciplines today. But what does a data scientist do? And how can you break into the field? Read More and business professionals. Anyone can benefit from the many functions that you’ll find tucked away in Excel Find Anything in Your Excel Spreadsheet with Lookup Functions Find Anything in Your Excel Spreadsheet with Lookup Functions In a giant Excel spreadsheet, CTRL+F will only get you so far. Be clever and let formulas do the hard work. Lookup formulas save time and are easy to apply. Read More .

Has this article given you any creative ideas of how you can use Excel to solve problems in your own life? Have you ever used some of these Excel functions before in creative ways? Share your own ideas and stories in the comments section below, I’d love to hear them!

Image credit: Goodluz via Shutterstock.com

  1. Luke
    January 21, 2016 at 4:48 pm

    I have a spreadsheet that continually changes. Using COUNTIF, is there a way to look for blank cells even if the cell range contains no data at all.

    In other words, I'll want to count blank cells in future data rows down the road but I don't want to have to change the formula every time I add more data.

  2. Ethan
    November 25, 2015 at 9:35 pm

    For your FV – Future Value example you need to make sure you match the units of time in the interest periods (nper) with the unit used in your interest rate.

    In your example (I'm assuming) you have an annual interest rate but you use months as the unit of time. So when you plug the number of months in the formula you are actually telling it to calculate the future value for that many *years* since your interest rate is yearly.

    You can easily fix it by either dividing the interest by 12 (making it a monthly rate) or by dividing the number of months by 12 (giving the number of years).

Leave a Reply

Your email address will not be published. Required fields are marked *