Unit 8 - Excel Functions, Sorting, and Filtering

Excel offers over 200 functions for use in worksheets. A function is a predefined formula. This can be as simple as finding the sum of a group of cells or as complicated as computing the depreciation of a business asset or returning complex statistical information. Excel groups functions into specific fields of study such as statistics or finance. Most functions require data to manipulate. The values placed in the formulas are called arguments. The arguments are enclosed in parentheses after the function name; for example a simple summation of a column of text would look like this =sum(B6:B9). An argument may be a constant value (such as 3 or 9.5), a cell name as in F7, or a cell range. A range is a rectangular series of connected cells. The image below demonstrates a range of cells, they are the cells with the double line box around them, cells B6 to B9.



There is also a block of cells, the image below demonstrates a block of cells from B6:F10. The selected cells are highlighted except the starting cell which is clear. Blocks of cells are good for formatting and other features.




Sum

There are a number of ways to get to formulas. You can have them memorized like =sum(), =average(), =max(), =min() if they are simple. Or you can click the 'Insert Function' icon to the left of the 'Formula Bar'. Column G holds the total points for the term.

To use the SUM function:
o Highlight cell G6.
o Either enter the formula from memory or click the 'Insert Function' icon
o Make sure that 'All' is selected in the 'Or select a category:' box near the top of the window.
o Scroll through all the Excel function names in the 'Select a function:' box. Stop when the SUM function name is visible.
o Click once on the word SUM.
o Below the box with the function names is a brief description of the SUM function and the arguments it requires: number1, number2,... means that it needs a group of numbers to add together. The numbers may be individual cells, constant values, or a range of cells. Click OK.



The next window is for you to enter the arguments for the function. You could list the individual cells to be added (B6+C6+D6+E6+F6), but instead, enter the range of cells like this B6:F6 and click OK. This is much shorter and faster.

The sum of the scores (709) is displayed in cell G6. The Edit bar shows the actual data in the cell is =SUM(B6:F6). See the above image. The equals sign tells Excel that the text following is a formula and is to be treated differently. With formulas the function result will be automatically adjusted whenever the data is changed.

Function arguments may also be entered by clicking and dragging the mouse. Sometimes this is easier if you don't remember the exact cell names of all the arguments.

o Highlight cell G6.
o Click the 'Insert Function' button
o Select SUM from the list of functions or the list of Most Recently Used functions.
o Click OK.
o The 'Function Arguments' dialog box appears. Now go to cell B6 and get the large white plus sign, left click and hold it down. Drag it down to cell G10 and release the left mouse button. The cells B6 to G10 are now highlighted with a marque and we see the range also in the 'Function Arguments' dialog box.
o Click OK.

The cells G7 to G10 have been formatted with the sum function.

Copy the SUM function the fast way using Form Fill:
o Highlight cell G6 that already has the sum function in place
o Hover the cursor over the small square in the bottom right of the highlighted cell
o When you see a black plus sign left click and hold it
o Drag the cursor to cell G10 and release
o All the cells below cell G6 are now populated with the formula to sum the total points

CAUTION!!!!!
If the cell contents of the original formula change context, that is describe some other concept or are blank, errors will occur. You can only use this method of copying when the formula and data are uniform for the entire section being copied.

Average, Min and Max

The AVERAGE, MIN and MAX functions are similar to the SUM function. AVERAGE finds the average value of a group of cells, MIN finds the smallest (minimum) value and MAX finds the largest (maximum) value. Like the SUM function, AVERAGE, MAX, and MIN require a group of cells as the argument.

To insert the AVERAGE function:
o Highlight cell B12 in the Grades Sheet.
o Type in =average() or hit the 'Insert Function' icon and find the 'AVERAGE' function in the menu box
o Indicate the range B6:B10 by either typing it in the argument box or highlighting the range of cells to be averaged (Be sure you do NOT include the total score possible when you are computing the average of the scores.)
o Click enter if you typed in the formula or OK if you used the 'Insert Function' icon.

Use the same steps to insert a MAX and MIN function.

Financial Functions PMT, NPER & FV

The PMT (Payment) function computes the payments necessary to repay a loan. The required arguments are the interest rate, the number of payments, and the amount of the loan. Excel will assume all time periods are the same, i.e. if the interest rate and the number of payments are specified in years, the payment will also be in years.







To enter a PMT function in the above worksheet:
o Highlight cell B7.
o Click the 'Insert Function' icon
o In the 'Or select a category:' field choose the 'Financial' category then select the 'PMT' function and click OK.
o The 'Function Arguments' dialog box appears. The first argument required is the interest rate. Type B4/12. (The interest rate must be the rate per month to get a monthly payment as a result)
o Click in the box for the second argument, Nper (number of periods).
o Enter B5*12 which is the number of monthly payments.
o Click in the box for the third argument, Pv (present value).
o Enter B3. Click OK.

The correct monthly payment is $509.52. If you enter different values for the cost of the car, the interest rate, or the number of years necessary to repay the loan, the payment will be automatically adjusted.


The NPER function calculates the number of periods needed for a constant payment to reach a certain amount assuming the interest rate stays the same. It is similar to the PMT function except the payment amount is known and the number of payments is computed.



To enter the NPER function:
o Highlight cell B8.
o Click the 'Insert Function' icon
o In the 'Or select a category:' drop down menu select 'FINANCIAL'
o Select 'NPER' then click OK.
o The first argument required is the interest rate (Rate). Type B4/12. (Once again, the annual rate must be changed to a monthly rate by dividing it by 12.)
o The second argument box will contain the amount of the monthly deposit (Pmt). Click on the box and type -B3 since this represents liability.
o The third box is for the current value of the account (Pv). Click the box and type B5.
o The last argument that will be entered is the future value (Fv), or what the value of the account will be at the end of the payments. Click on the fourth box and type B6.
o Click OK.

The answer is 1062 months. To change this to years highlight B8. In the Formula Bar modify the function to be =NPER(B4/12, -B3, B5, B6)/12.

Use the Future Value function to see what the balance in an account will be after a certain period of constant deposits at an unchanging interest rate.



To enter FV function:
o Highlight cell B8.
o Click on the 'Insert Function' icon
o Click on 'FV' from the Financial Functions category and then click OK.
o The first argument required is the interest rate (Rate). Type B4/12. (Once again, the annual rate must be changed to a monthly rate.)
o The second argument box will contain the number of periods. Since the deposit is made every month, the number of years must be multiplied by 12 to get the number of periods. Click on the box and type B6*12.
o The third argument box will contain the amount of the monthly deposit (Pmt). Click on the box and type -B3. The amount is negative since it represents a liability.
o The last argument that will be entered is the current account balance (Pv). Click on the fourth box and type B5.
o Click OK. The answer should be $3681.25.

Making Decisions --- The IF Function

One of the most powerful Excel functions is IF. This function makes a decision based on values in the worksheet. There are three arguments to the IF function: 1) a logical test; 2) the value to insert if the test is true; 3) the value to insert if the test is false. The logical test is a relational expression that compares two values and returns a value of TRUE or FALSE. The values may be constants, cell names, or the results of formulas or functions. Logical tests are compared with one of the following relational operators:

<.......(less than)
<=......(less than or equal to)
>.......(greater than)
>=......(greater than or equal to)
=.......(equal to)
!=......(not equal to)

Some valid logical tests are:
6 < 9...........................(TRUE)
7 + 4 != 12.....................(TRUE)
3 * 12 < 4 * 9..................(FALSE)
B3 < 100........................(Depends on the value in B3)
(C9 + C10) / 12 = 4.............(Depends on C9 and C10)
AVERAGE(A3:A10) >= 70...........(Depends on cells)
PMT(B5/12, B6*12, -B4) < 300....(Depends on cells)

The second and third arguments for the IF function are what Excel will enter in the cell containing the formula depending on the outcome of the logical test. The second argument is inserted if the logical test is true and the third if the test is false. Numbers, formulas, or functions may be used for the second (true) and third (false) arguments. Text and blank spaces must be enclosed in quotation marks. This is in one of the homeworks!!!

Assume this worksheet:


There is free shipping on orders of $100.00 or more. Orders less than $100.00 have a $5.00 shipping charge. The IF function can be used to examine the order amount and put the proper charge in the cell for the shipping costs.

To insert the IF function:
o Highlight cell C4.
o Click the 'Insert Function' icon (or you can type in =if( to enter the formula)
o In the 'Or select a category:' field select 'Logical'
o In the 'Select a function' menu box click the 'IF' function
o The 'Function Arguments' dialogue box appears. The question is this: Is the Amount $100 or more in cell B4?. The logical test is (B4 >= 100).In the 'Logical_test' field enter B4>=100 hit tab.
o The second argument ('Value_if_true') is what should Excel put in the cell when the logical test is true. In the box for 'Value_if_true', type 0.
o The third argument ('Value_if_false')is what should go in the cell when the logical test is false. If the order is less than $100, the shipping charge is $5.00. In the box for the third argument, type 5.


A second 'IF' example:



Let's assume the company now decides to use weight to determine the shipping cost. The worksheet with the added weight column is shown above. There is still no shipping charge on orders of $100 or more. For orders of less than $100, the shipping charge is now 0.85 cents per pound.

The IF function in cell D4 needs to be changed. Highlight D4 and click on the 'Formula Bar'. The first two arguments will remain the same, but the last argument will be the weight multiplied by $0.85 cents (C4*.85). The final entry in D4 is: =IF(B4>=100,0,C4*.85)

The formula for the total amount in column E is the 'Amount' + 'Shipping'.

To insert text as one of the results of an IF statement, the text needs to be enclosed in quotation marks. See the following image representing a grade sheet for a class. An IF function is used to see who would pass or fail. The second argument is 'Pass' and the third argument is 'Fail'.



Sorting and Filtering Data

Excel includes many ways to work with the data such as sorting and filtering.

Sorting

A common Excel operation is sorting by field. In the worksheet above, cities are arranged by population. Cities could be sorted alphabetically, by state, by population, or by amount of change.

To sort the table so the cities are in alphabetical order:
o Highlight the information, including the header rows (A2:E27).
o Select the 'Data Tab'
o Click 'Sort'
o The 'Sort' menu box appears.
o Under the 'Column' heading in the 'Sort by' drop down box select 'A' or click the 'My data has headers' box and then select 'City'.
o Under the 'Order' heading select 'A to Z' or alphabetical order. Then click 'OK'

To sort the cities table so that the 2010 census goes from largest to smallest:
o Highlight the information, including the header rows (A2:E27).
o Select the 'Data Tab'
o Click 'Sort'
o Under the 'Column' heading in the 'Sort by' drop down box select '2010 Census'.
o Under the 'Order' heading select 'Largest to Smallest'. Then click 'OK'

To put the states in alphabetical order:

o Highlight the information, including the header rows (A2:E27).
o Select the 'Data Tab'
o Click 'Sort'
o Under the 'Column' heading in the 'Sort by' drop down box select 'State'.
o Under the 'Order' heading select 'A to Z'. Then click 'OK'

You may want to sort according to more than one field like first sorting the states then their cities also in alphabetical order.
o Highlight the information, including the header rows (A2:E27).
o Select the 'Data Tab'
o Click 'Sort'
o Under the 'Column' heading in the 'Sort by' drop down box select 'State'.
o Then click the 'Add Level' button and a new row appears with 'Then by' at the beginning
o Under the 'Column' heading in the 'Then by' drop down box select 'City'
o Select 'A to Z' for the order of both the fields
o Click 'OK'

Filtering Information

While sorting allows you to put information in a particular order, filtering lets you isolate information based on some criteria. Perhaps you are interested in all the cities within a certain state or all the cities with populations over a certain number. Once isolated, the information can be copied into another table of its own. To use the Excel filtering operations, your table must have a header row. Excel will assume the top row of the selected area is the header row and not include this information in the filtering.

To only see cities in California highlight any cell in the table select the Data Tab->Filter and click the down arrow under the 'State' heading cell B2. A drop down menu appears. At the bottom are all of the field entries for the states and the very first line is '(Select All)'. Everything is selected to start with. First un-check '(Select All)' and then check 'CA'. Click 'OK'. Now only the cities in California are shown. Note the column heading that we sorted on now has a little funnel/filter next to the column we are filtering on.

The table shrinks to four cities, all of them in California. Note that the rows are no longer numbered consecutively. The rows for states other than California have not been deleted from the table; they are just hidden for now. To change the display to show only the cities in Texas click on the down arrow in cell B2 again. Choose TX from the list of states.

Filtered lists may be copied to other locations and the hidden rows are not included:
o Rename a new sheet 'Lists'.
o In cell A1 on the Lists sheet type California Cities and press Enter.
o Return to the Largest Cities sheet.
o Click the down arrow on the right side of cell B2 again.
o Un-check '(Select All)' and then check 'CA'
o Highlight the rows containing California cities and their headings press CTRL + c to copy the data
o Go to the new sheet 'Lists' and highlight cell A1 and press CTRL + p to paste the data
It should look like this.



Before doing a filtering operation that does not involve states it is necessary to un-hide all the rows that are hidden as a result of the last operation. Click on the down arrow at the right side of cell B2 again. Check the '(Select All)' box to see all the data.

Filtering numerical data differs from text data. With text data, you usually try to match certain groups of characters exactly. With numbers, you can match a range of values.
o Select the 'Data Tab'
o Highlight the cells to be filtered and then click 'Filter'
o Click the arrow next to the '2012 Population' column header
o Click 'Number Filters'
o Select 'Greater Than'
o The 'Custom AutoFilter' menu appears. Enter 1,000,000 in the field next to 'greater than'
o Click 'OK'
The rows that appear, including headings, are those with cities over 1 million. When finished using AutoFilter, turn it off to view the entire table.

To return to the original table:
o Select the '2012 Population' column header drop down menu. It should have an arrow with a little funnel next to it
o Click 'Clear Filter Form'
o The table is back in its original form