The Average Function in Excel
If you're trying to work out an average, you're trying to calculate what the most common value is. For example, if a class of eight students took exams, you may want to know what the average exam score was. In other words, what result most students can expect to get. In order to calculate an average, you'd add up all eight exam scores and divide by how many students took the exam. So if the total for all eight students was 400, dividing by 8 would get you 50 as the average grade. If any students were below the average, you can tell at a glance.
In Excel , there is an easy way to calculate the average of some numbers - just use the inbuilt Average function.
Start a new spreadsheet and enter the following exams scores in cells A1 to A8, as in the image below:
Click in cell A9, and we'll see how to use the Average function in Excel 2007. There are two ways we can do this. Try method 1 first.
Method 1
Next to the formula bar, you'll see an FX button. This is the Formula Wizard:
When you click the FX button, you'll see the Insert Function dialogue box appear:
The Insert Function dialogue box shows a list functions. These are the just the common ones. To see more functions, click the drop down list to the right of Select a category. The one we want is displayed under Select a function, though - Average. Click on this, and then click OK.
When you click OK, another dialogue box appears. On this dialogue box, you select the data that you want to include in your function:
If you look in the Number1 box, you'll see Excel has guessed which cells we want to use for our Average function - A1:A8. It evens gives the answer to the Function - 6.625.
Click OK to insert the function.
Method 2
The second way to enter a Function in Excel is through the panels on the Ribbon. Try this:
- Click inside cell B9 on your spreadsheet. This is where we'll place the Average for the cells A1 to A8.
- Click the Formulas menu at the top of Excel
- Locate the Function Library panel. Here it is in Excel 2007
And here it is in later versions:
As you can see, in Excel functions are split into categories. The Average function is in a few places. The easiest way to use Average is with AutoSum. Click the down arrow on AutoSum to see the following:
Now click Average from the menu. Because the answer is going in cell B9, Excel doesn't know which cells you want to use in the function, so it can't give you a quick answer. AutoSum is good when the data is in the same row or column. But when it's not, you have to tell it what to calculate.
So click inside cell A1 and you'll see the cell selected
Hold down your left mouse button over the bottom right blue square, and drag to cell A8:
Excel fills in the cells for your function. Let go of the left mouse button, and then press the Enter key on your keyboard. The correct answer is place in cell B9:
You can also find the Average function on the More Functions menu. Click Statistical, and you'll see it there:
Of course, once you know the correct function, you could simply type it all out in the Formula bar yourself!
Try this exercise.
Exercise
You start your own online business and find that sales for the first week are these:
Monday £120.45
Tuesday £187.43
Wednesday £106.87
Thursday £143.69
Friday £117.52
Saturday £87.93
Sunday £92.12
Use a function to work out how much you earned, on average, each day.
The Date Function in Excel
There are a numbers of different reasons why you would want a Date or Time function in a spreadsheet: If you're running your own company, you might want to record when an order was received and when it was processed. You could then calculate the difference between the two, so that you check how fast the orders were being processed. We'll do that now.
The Excel Date Function
As an example of how to use date functions in Excel, we'll contruct as simple spreadsheets for an order form. We'll enter the date an order was taken, the date the order was sent, and how long it took to be processed. So, to make a start, create the spreadsheet below :
Click inside cell A2, and we'll enter a date. To enter a date, Click on the Formulasmenu at the top of Excel. Then locate the Function Library panel. From the Function Library panel, click on Date & Time:
As you can see, there's quite a lot of Date and Time functions! Click on Date from the menu, and you'll get the following dialogue box:
You're now being asked enter a full date.
- In the Year box, enter 2010
- In the Month box, enter the number 4
- In the Day box, enter the number 15
- Click the OK button
- Excel will enter the Date in your selected cell, A2 for us
Notice the DATE Function in the Formula bar:
=DATE(2010, 4, 15)
Between the round brackets of DATE, the Year comes first, then the Month, then the Day.
If you want to format your date as say Monday 15th of April, then you need to click on the Home tab from the Ribbon at the top of Excel. Locate the Number panel, and you'll see Date already displayed:
Click the down arrow to see more options:
Click the Long Date item. Or click on More at the bottom to see some more Date formats to choose from. Your spreadsheet will then look like this:
In cell B2, under your Date Order Sent heading, enter another Date Function. This time, have the date read May 3, 2010:
In cell C2, under Time Taken, we'll work out how many days the order took to be sent out.
The Days360 Function in Excel
When you want to work out how many days there are between two dates, the function to use is Days360( ). We want to work out how many days there are between the 15th of April 2010 and the 3rd of May 2010. So click inside cell C2 and do the following:
Click on the Formulas tab at the top of Excel. Then locate the Function Librarypanel. From the Function Library panel, click on Date & Time. From the menu, click on Days360(). You should see the Function Arguments dialogue box appear again. This time, it will look like this:
The Days360 function needs a start date and an end date. You can enter your cell references here. So in the Start_date box, enter A2. In the End_Date box, enter B2. If you enter the word True in the Method box, Excel will calculate using the European date system. Click OK, to return to your spreadsheet and you might see this:
(If your C2 cell has a lot of #### symbols in it, it means that your column is not wide enough. Widen the C column and they'll go away!)
The answer we got was January 18th 1900! The reason for such a bizarre answer is that we've formatted the C2 cell as a date. But the answer to the Days360 function is not a date - it's a number. If you have the same strange answer, then format your C2 as a number. Your spreadsheet will then look like ours below:
So the difference between the two dates is 18 days.
Entering dates can be fairly straightforward, like cells A2 and B2. But performing calculations with dates can be slightly more complex. To get you some more practise, here's an exercise.
Exercise
Use a Days360 function to work out how many days are left before your next birthday.
Instead of typing out the current date in say cell A2, you can use this inbuilt function:
=Now( )
The Now function doesn't need anything between the round brackets. Once you have today's date, you can enter your birthday in say cell B2.
Time Functions in Excel
There are a number of ways to enter the current time in an Excel spreadsheet. Try this:
- Click inside a blank cell on your spreadsheet
- Click into the Formula Bar at the top
- Type the following inbuilt function:
= Now( )
Hit the enter key, and you'll get the current date and time. If you only want the time, you can format the cell to get rid of the date part:
- Click on the cell that contains your Now() function
- From the Excel menu bar, click on Home
- Locate the Number panel, and you may see Time already set (it might sayCustom in later versions of Excel):
- Click the down arrow to see menu options
- From the menu, click on Time
- Click on More to see more Time options
Excel doesn't update the Time function every second, so it's not like a normal clock. But you can update the Now function to get the correct time. (Well, it's correct if your system clock is correct!)
The easiest way to update the Time in Excel is to click inside the cell that holds your Time formula. Then, from the Formulas menu, locate the Calculation panel.
Click the "Calculate Now" button, and Excel will update the time.
Excel also updates the time when you enter another calculation elsewhere in the spreadsheet. For example, click in any other cell on your spreadsheet. Now enter a simple formula like = 2 + 2. When you press the Enter key on your keyboard, Excel will update your time function as well.
A Timetable Project
In this Excel timetable project, we'll set ourselves some chores to do around the house. We'll plan an exact time to start a task, and how long it will take to finish. We'll be adding one time to another.
So create the same spreadsheet as the one in the image below (you don't need to use the same colours):
What we're going to do is enter a Start Time for our chores. This will be 9 in the morning. Then we'll estimate how long it takes to wash the pots, which we'll place in the Time Job Takes column. We'll add the "Time Job Takes" to the "Start Time" to get a new start time for the Hoover chore. But you'll see how it works as we go along.
The first thing to do is to format the Start Time column:
- Highlight the Start Time column, from cell B3 to cell B8
- From the Excel menu bar, click on Home
- Locate the Number panel:
Click on the arrow (circled above) to bring up the Format Cells dialogue box, and then click the Time category:
- Under Category on the left, click on Time. Under the time Types on the right, select the first one.
- Don't click OK yet, but have a look at the time format that Excel is going to enter:
Excel will enter the hours, then minutes and the seconds. We don't need the seconds. Unfortunately, this version of Excel doesn't give you a time format without seconds. To remedy this, click on Custom under the Category list on the left. Then, under Type, select "h:mm AM/PM", as in the image below:
Click OK when you're done.
We'll now enter our first time.
- Click on cell B3, then click inside the formula bar
- Type in 09:00 (the colon in between the numbers is important)
- Press the enter key on your keyboard
- Excel will now see cell B3 as a time - 9.00 AM
There is a simpler way to format a cell as Time, though. Try this:
- Click inside cell C3
- Click inside the formula bar
- Type in 0:15
- Press the Enter key on your keyboard
Because you included the colon (:), Excel knows that you want to format the cell as a time. The 0:15 then means 15 minutes (We'll assume that we're very fast at washing pots - it's all that practice!).
But your spreadsheet will now look like this:
If we started at 9.00, and the job took 15 minutes, the next start time will be 9.15. We can enter a formula for this:
- Click into cell B4 to highlight it
- Then click inside the formula bar
- Enter the following formula:
= B3 + C3
- Press the enter key
- Excel will place a time of 09:15 AM in cell B4
The start time for our next chore, then is 9.15 AM. We can use AutoFill for the rest of the B column:
- Click in to cell B4 on your spreadsheet
- Move your mouse the bottom right of cell B4, and the pointer will change shape. When you see the black cross, the AutoFill cursor, hold down your left mouse button and drag down to cell B9
- Let go of the left mouse button and Excel will AutoFill the other formulas
Because we haven't yet entered any other figures for the "Time job Takes" column, a time of 9.15 will appear in all the cells.
- Click onto cell C4 on your spreadsheet
- Now click in to the formula bar at the top, and type in 01:00 (meaning one hour)
- Hit the Enter key on your keyboard and Excel will change all the cells from B5 to B8 to a time of 10:15 AM
- Your spreadsheet should look like ours:
Complete the rest of the spreadsheet for yourself. Enter these times in the C column:
Rest: 30 minutes
Dust: 30 minutes
Windows: One hour
Rest 30 minutes
If you complete it all correctly, you should have a spreadsheet like ours in the image below:
In the image above, you'll notice that there is a time in cell B9 of 12:45 PM. You should easily be able to get the same figure in your spreadsheet!
Working with date and times can be quite tricky. But it's well worth getting the hang of. We'll move on, though, and have a go at financial functions in Excel.
Financial Functions
The financial function we're going to explore is called PMT( ). You use this function when you want to calculate things like the monthly payment amounts on a loan, or how much per month a mortgage will cost you. We'll use it to work out how much per month a loan will cost us. Here's what we'll do.
We've decided to take out a loan of ten thousand pounds from our friendly banker. We're going to be paying it back over 5 years. The question is, how much per month is this going to cost us?
The PMT( ) Function in Excel
The PMT( ) function expects certain values in between its two round brackets. The values that go in round brackets are known as arguments. The arguments for the PMT( ) function are these:
PMT(rate, nper, pv, fv, type)
Only the first three are needed, and you can miss the final two out, if you like.
We'll work out our monthly loan costs with the help of the PMT( ) function. First, create a new spreadsheet like the one below:
If you look at cell B1 on the spreadsheet, you'll see a figure of £10, 000. This is the amount we want to borrow. The labels on Row 3 show what else we need: An interest rate, the number of payments we'll make over the 5 years, the present value of the loan, the amount we'll have to pay back each month, and the total amount paid back after 5 years. But we only need the first three for our PMT() function.
In cell A4, we'll need an interest rate. In cell B4 we'll need the number of payments, and in cell D4 we'll need the Present Value of the loan. First is interest rate.
Imagine that the interest rate given to us by the bank is 24 percent per year. For the PMT( ) function, we need to divide this figure by 12 (the number of months in a year) So try this:
- Click into cell A4 on your spreadsheet
- Enter the following formula:
= 24% / 12
- Hit the enter key to see the answer appear, as in the image below:
Now that we have an interest rate, the next thing we need for the PMT( ) function is how many payments there are in total. We have to pay something back every month for 5 years. Which is a simple formula. So,
- Click into B4 on your spreadsheet and enter the following:
= 12 * 5
- Hit the enter key to see a figure of 60 as the answer.
This figure of 60 is for the second argument of the PMT( ) function - the nper. This is just the number of payments.
Now that you have a figure in cell A4 (rate), and a figure in cell B4 (nper), there's only one more to go - the Present Value (pv).
The Present Value of a loan, also known as the Principal, is what the loan is worth at the present time. Since we haven't made any payments yet, this is just 10, 000 for us.
- Click into cell C4 on your spreadsheet and enter the following:
= B1
- Hit the enter key
- You'll see a figure of 10, 000 appear, and your spreadsheet should now look ours below:
OK, we now have all the parts for our PMT() function: a rate (A4), an nper (B4), and a pv (C4). Try this:
- Click into cell D4 on your spreadsheet
- Enter the following function:
=PMT(A4, B4, C4)
Hit the enter key on your keyboard, and you'll see the monthly amount appear. The figure you should have is -£287.68. The reason there is a minus sign before the total is because it's a debt: what you owe to the bank.
But this is what your spreadsheet should look like:
The only thing left to do is see how much this loan will cost us at the end of 5 years. All you need to do here is multiply the monthly amount in cell D4 by the number of payments in cell B4. Enter your formula for this in cell E4, and you spreadsheet will look like ours below:
So a ten thousand pounds loan, at the interest rate the bank is offering, means we'll have to pay back just over 17 thousand pounds over 5 years.
Tweaking the Values
We can change the spreadsheet slightly to give us more control. For your figure in cell B4, the number of payments, you entered 12 * 5. This is 12 months multiplied by 5 years. But what if we wanted to pay the loan back over 10 years, or 15? How much will our monthly payments then be? And will be the final cost of the loan?
Also, the interest rate seems a bit high. What if we can get a better rate elsewhere?
By making a few changes to or spreadsheet, we can amend these values more easily. First we'll need two new rows.
Inserting New Rows in Excel
We need to insert new rows in our spreadsheet. To insert a new row, click into cell A2. Then click on the Home tab at the top of Excel. Locate the Cells panel, and click theInsert item:
From the Insert menu, click on Insert Sheet Rows:
Excel will insert a new row for you. Do this again to get two blank rows. Add two new labels, Num of Years and Interest. Your spreadsheet sheet will then look like this:
Adapting the PMT Formula
We can adapt the formulas we've entered so far, in order to make them more usable. As an example, we'll adapt the interest rate.
To get the interest rate for cell A4, we entered a formula:
= 24% / 12
Instead of having the interest rate in cell A4, however, we can place it at the top, in cell B3 on our new Row. We can then alter the interest rate by simply typing a new one in cell B3. To clear all that up, try the following:
- Click inside cell B3, which is the Interest cell in the image above
- Click inside the formula bar
- Type in = 24%
- Cell B3 should now read 24.00% (In Excel 2010 and 2013 you may have to format the cell to a Percentage value from the Home tab then the Numer item.)
To change the formula for your interest rate click inside of cell A6. Change the formula from this:
= 24% / 12
to this:
= B3 / 12
Hit the enter key on your keyboard and nothing should change on your spreadsheet. But the difference is that you can enter a new interest rate in cell B3, and see how this effects the loan amounts. Try it out by typing 23% in cell B3:
As you can see, the interest rate has changed to a rather long figure. But notice the Monthly Amount - it has gone down to £281.90. The total amount we have to pay back has changed, too. Play around with the interest rate in cell B3, just to get a feel for how it works.
Exercise
In cell B6 of your spreadsheet, you have the following formula:
= 12 * 5
This calculates the number of months for the loan. Change this formula so that the number of years is coming from B2. Your finished spreadsheet should look like ours below:
If you play around with the values in cells B1, B2 and B3 you should be able quickly see the new loan repayments.
Review Number Three
Study the following spreadsheet:
The spreadsheet is the school exam marks of 8 students. A total of 8 subjects were taken. For this review, work out the Averages for all 8 subjects studied, which is in the K column above. Also, work out the Overall Averages for each student (row 11 above).
The first thing to do is to recreate the spreadsheet above. You can then use one of the methods for working out Averages that you have explored in this section. You're going to be needing your completed spreadsheet in the next section - so don't skip it!
Comments
Post a Comment