Excel 2003 Advanced Calculations

Microsoft Excel 2003 Excel Advanced Calculations OTS PUBLICATION :

E13 • REVISED 12/4/2006 • TRAINING@ TOWSON . EDU • OFFICE OF TECHNOLOGY SERVICES =Shortcut

=Advice

=Caution

Introduction Excel has over 300 different built-in formulas, or Functions as they are called. Functions that enables you to create simple to complex Process Summary calculations easily and effectively. These functions are organized into categories to make them easy to find. The categories include, Statistical Functions Financial, Date & Time, Math & Trig, Statistical, Lookup & Reference, 1. AVERAGE Database, Text, Logical and Information. 2. MEDIAN 3. MODE This document will cover how to use some of the more commonly used functions using the Insert Function Wizard. This document will 4. MAX 5. MIN also cover AutoCalculate, Relative vs. Absolute references, the use of the AutoSum button, and how to create a 3D formula. 6. COUNT

AutoCalculate You can use the AutoCalculate feature when you need to know the sum of a range of values without having to add a formula to the Worksheet. Select any range of cells (Figure 1, 1) and Excel displays the answer on the Status Bar (Figure 1, 2). In addition to displaying the sum of a range you can also choose between six basic functions, such as Average, Count, Count Nums, Max Min and Sum. To use a different AutoCalculate function, right-click on the current answer on the Status Bar and select a desired function from the shortcut menu. If the Status bar is not visible, select View > Status Bar from the menu bar.

Logical Functions 1. IF 2. Nested IF Lookup Functions 1. VLOOKUP 2. HLOOKUP Financial Functions 1. PMT (Payment) 2. FV (Future Value) Other 1. 3D Formula Figure 1

1

2

© 2005 Towson University -This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivs License. Details available at http://www.towson.edu/OTStraining. 410-704-4070.

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

Relative Reference vs. Absolute Reference Relative Reference In Excel, a Relative Reference is the address of a cell, or range of cells, that changes when it is moved or the relationship to it has changed (e.g. if you insert additional rows or columns). For example, in a spreadsheet, a cell with a relative reference changes its formula when copied elsewhere.

Figure 2

Notice the cell references adjust according to their location when you copy the formula in E5 down though E8

Absolute Reference Sometimes, however, in a calculation you need to anchor a cell reference so that it doesn’t “move on you” when you copy the formula to other locations. In order to do so, you need to make the reference Absolute so that the cell address does not change. To make a cell reference Absolute, you must type a dollar sign ($) before the column reference and another dollar sign ($) before the row reference. You can also just press F4 on the keyboard – The F4 key lets you toggle between relative, absolute and two types of mixed cell references. If you press Ctrl + ~ (tilde) on the keyboard, Excel toggles between displaying the values and formulas. Figure 3

Notice the first cell reference adjust according to the new row, but the second reference is anchored to H1, the commission value

2

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

Functions Formulas can be made much more efficient by using worksheet functions. There are over 300 different Functions in Excel. Functions can be thought of as being made up of three parts: an equal sign, a function name and a list of arguments enclosed in brackets. There are several ways you can insert your functions, but the most common methods are:

• Select Insert > Function from the menu bar. • Click the Insert Function button

on the formula bar.

• Press Shift + F3 on the keyboard to display the Insert Function dialog box. • Type the function completely from scratch.

Using the Insert Function Dialog Box Using the Insert Function dialog box helps you “walk through” setting up your new formula. This dialog box is designed to try and help you find the correct function. Select Insert > Function from the menu bar and the Insert Function Dialog box displays. If you do not know the exact name of the function you need, you can scroll the ALL categories list or filter the list of available functions by selecting a specific category from the dropdown list. After you select a function and have clicked OK, the Function Arguments dialog box displays. This is where you indicate the arguments that are required by that function. Figure 4

Functions are organized into categories. You can find any function in the All category.

3

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

About Arguments Arguments can be numbers, text, cell references (e.g. A1:A15), named ranges (e.g. GradeTable), arrays, formulas or even other functions (a.k.a. Nested Functions). Arguments that are displayed in bold are required. The dialog box also displays the Formula result (Figure 5, 1) so that you have an idea if you are on the right track before accepting the formula by clicking the OK button. If you need help with any function, click Help on this function (Figure 5, 2). You can press F1 on the keyboard if you need any help with a particular function. Figure 5

This function requires only one argument

1

2 The Sum Function To add numbers in Excel you can use the SUM function. The SUM function returns the total of all the numbers in a range of cells. This formula can be typed from scratch and the cell range does not need to be adjacent. The SUM Functions is by far the most commonly used Function in Excel. Basic Concept: =SUM(number1, number2,number3,...) Example:

=SUM(B5:B8)

Example 2:

=SUM(B5,C8,D12,E15) Figure 6

The Sum function in this example is adding up the numbers in cells B5 through B8

4

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

AutoSum Button Since one of the most common tasks for spreadsheets is to sum a range of values, Excel has a special button that helps you do just that. The AutoSum button is located on the Standard toolbar. As step one, before you click the AutoSum button, it is recommended that you select the cell where you want the answer. When you click on the AutoSum button, the Sum function and a suggested range to be added are displayed and highlighted in the formula bar. You can change this range if it is not correct by either typing or drag-selecting the correct range. If you click the down arrow next to the AutoSum button, you can also use other commonly used Functions such as AVERAGE, COUNT MAX, MIN.

AVERAGE Function Another common calculation is Excel is the AVERAGE function (Figure 7, 1). The AVERAGE function is a Statistical function which returns the arithmetic mean of a list of values. In other words, it adds up the total value of all the cells selected and divides it by the number of cells selected. Basic Concept: =AVERAGE(number1, number2,number3,…) Example:

=AVERAGE(B6:F6)

MEDIAN Function Another type of statistical function is the MEDIAN function (Figure 7, 2). The MEDIAN function returns the middle of a distribution (half the scores are above the median and half are below the median) of all the numbers evaluated by the formula. Basic Concept: =MEDIAN(number1, number2,number3,...) Example:

=MEDIAN(B6:F6)

MODE Function A third type of statistical function is the MODE function (Figure 7, 3). The MODE function returns the most common or frequent value in a data set. It is possible that there is no match and Excel displays #N/A (Figure 7, 1). Basic Concept: = MODE(number1, number2,number3,...) Example:

=MODE(B6:F6)

COUNT Function A fourth type of statistical function is the COUNT function (Figure 7, 4). The COUNT function counts the total number of cells that contain numbers or data. Basic Concept: =COUNT(value1,value2,...) Example:

=COUNT(B6:B17)

5

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

MAX Function Another type of statistical function is the MAX function (Figure 7, 5). The MAX function returns the largest value of all the numbers evaluated by the formula. Basic Concept: =MAX(number1,number2,number3,…) Example:

=MAX(B6:B17)

MIN Function Another type of statistical function is the MIN function (Figure 7, 6). The MIN function returns the smallest value of all the numbers evaluated by the formula. Basic Concept: =MINnumber1,number2,number3,…) Example:

=MIN(B6:B17)

Figure 7

The formulas below are all using the grades listed in cells A2 through A8. Each formula provides a different statistical answer

1

2

3

6

5

6

4

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

TODAY Function The Today function is a volatile Date and Time function that returns the current date (updates when file is opened or the worksheet is recalculated). This function does not require any arguments, but you must still include the empty parentheses after the function name (Figure 8, 1). Basic Concept/Example: =TODAY() It is probably easier to type the TODAY function from scratch. If you do, make sure you include the open and closed parenthesis after the function name – no argument is needed for this function.

NOW Function Returns the current date and time (updates when file is opened or the worksheet is recalculated). This function does not require any arguments, but you must still include the empty parentheses after the function name. Basic Concept/Example: =NOW() It is probably easier to type the NOW function from scratch. If you do, make sure you include the open and closed parenthesis after the function name – no argument is needed for this function.

Figure 8

1

7

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

IF Function The IF function is a Logical function that is designed to return one value if a condition you specify evaluates to be TRUE and another value if it evaluates to be FALSE. In other words, the function can only give one of two possible answers. For example, if you exceed the sales quota, you get a bonus, if not, you get nothing; or as show in Figure 9, if the value in the cell E7 is greater than cell F7, multiply cell E7 by 10%, or display 0 (zero). Basic Concept: =IF(logical_test, value_if_true, value_if_false) Example:

=IF(E7>F7,E7*0.1,0) Figure 9

When using the Insert Function dialog box, notice the formula is entered in the formula bar as well as in the selected cell

Nested IF Function A Nested function is simply a function within another function. The result returned from one function is used as the argument of another function. A nested formula can contain up to seven levels of arguments and do not need to be IF functions. Basic Concept: =IF(logical_test,=IF(logical_test, value_if_true, value_if_false)) Example:

=IF(I9=1,H9*0.1,IF(I9=2,H9*0.08,H9*0.07))

8

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

VLOOKUP Function The VLOOKUP function is a Lookup function that searches vertically for a value in the leftmost column of a (lookup) table and returns a value in the same row from another column you specify (Figure 10). The values in the lookup column must be sorted in ascending order. Notice the Table_array reference is entered as absolute (Figure 10, 1). See also the Relative Reference vs. Absolute Reference section of this document. It is recommended to make your Table_array reference absolute when using any lookup function so that you can easily copy the formula. You can quickly anchor a reference by pressing the F4 key on the keyboard. Basic Concept: =VLOOKUP(lookup_value, table_array, col_index_num,range_lookup) Example:

=VLOOKUP(F5,$B$17:$C$29,2)

Figure 10

The VLOOKUP function is looking up the Avg Score in cell F5, then looks in the Letter Grade Table below and returns the associated letter grade in the second column of the same table. An Avg Score of 88% returns a grade of B+

1

HLOOKUP Function The HLOOKUP function is another Lookup function, but this one searches horizontality for a value in the top row of a table and returns a value in the same column from a row you specify. The values in the lookup row must be sorted in ascending order. Basic Concept: =HLOOKUP(lookup_value, table_array, row_index_num,range_lookup) Example:

=HLOOKUP(F5,$B$34:$N$35,2)

9

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

PMT Function The VLOOKUP function is a Financial function that calculates the payment for a loan based on constant payments and a constant interest rate (Figure 11). Note: The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of monthly payments. Basic Concept: =PMT(rate,nper,pv,fv,type) Example:

=PMT(B5/12,B6,B8,0,1) Figure 11

The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. Only the Rate, Number of Periods and Present Value fields are required

FV Function The VLOOKUP function is a Financial function that calculates the future value of an investment based on periodic, constant payments and a constant interest rate (Figure 12). Note: The annual interest rate is divided by 12 because it is compounded monthly. Basic Concept: = FV(rate,nper,pmt,pv,type) Example:

=FV(B18/12,B19,B20,0,1) Figure 12

The FV function calculates the future value of an investment based on periodic, constant payments and a constant interest rate. Only the Rate, Number of Periods and Payment fields are required

10

M ICROSOFT E XCEL 2003: E XCEL A DVANCED C ALCULATIONS

3D Formula Is a special formula that typically refers to the same cell or range on multiple worksheets. This type of formula is also sometimes called a “cubed formula”. A 3D formula is a special formula that typically refers to specific cells across multiple worksheets. This formula is also sometimes called a “cubed formula”. A 3D formula can calculate a running total across worksheet by simply adding the desired cells (Figure 13, 1), or it can use another function to calculate across worksheets, such as the SUM function (Figure 14, 1). Basic Concept: =Sheet1Name!Cell1Name+ Sheet2Name!Cell2Name+ … Example1:

=January!D5+February!D5+March!D5

Example2:

=SUM(January!D5,February!D5,March!D5) Figure 13

1

This 3D Formula calculates a running total across the January, February and March worksheets using operators (plus signs)

Figure 14

1

This 3D Formula also calculates a running total across the January, February and March worksheets but is using the SUM function

11