Excel Formulas and Functions

(1)

Excel Formulas and

Functions

D a t e : 7 / 3 1 / 1 2 a n d 0 8 / 0 1 / 1 2 T i m e : 8 : 0 0 A M - 1 2 : 0 0 P M

L o c a t i o n : I C O E L a b s - B u i l d i n g B

Fiscal Development Services

Excel formulas and functions that all business professionals should know. This training will set the stage for future trainings that will help you take your data analysis to an advanced level.

Presented By: Carlos Zamora, Diane Faulkner and Carmen M Zamora

(2)

1 | P a g e

ROUNDUP Function ... 29 ROUNDDOWN Function ... 30 MROUND Function ... 30 CEILING Function... 31 MOD Function ... 32 SUMIF Function ... 32 SUMIFS Function ... 33 SUMProduct Function ... 34 SUBTOTAL Function ... 35 AVERAGE Function ... 36 MAX Function ... 36 MIN Function ... 37 Information Functions ... 37 LEN Function ... 37 Row Function ... 38 ROWS Function ... 38 COLUMN Function ... 39 COLUMNS Function ... 39 ISBLANK Function ... 40 ISNUMBER ... 40 Lookup Formulas ... 41 IF Function ... 41 AND Function ... 42 OR Function ... 43

(4)

3 | P a g e

VLOOKUP Function ... 43

HLOOKUP Function ... 44

MATCH Function ... 45

INDEX Function ... 46

Combining the INDEX & MATCH functions ... 47

OFFSET Function ... 47

(5)

4 | P a g e

Introducing Formulas and Functions

Formulas are what make a spreadsheet program so useful. If it weren’t for formulas, a spreadsheet would simply be a glorified word-processing document that has great support for tabular information. You use formulas in your Excel worksheets to calculate results from the data stored in the worksheet. A formula is entered into a cell. It performs a calculation of some type and returns a result, which is displayed in the cell. Formulas use a variety of operators and worksheet functions to work with values and text. For example, you can see multiple scenarios quickly by changing the data in a worksheet and letting your formulas do the work.

Formulas can consist of any of these elements:

 Mathematical Operators (example: additions and multiplication)  Cell References (including named cells and ranges)

 Values or Text

 Worksheet functions (example: SUM or AVERAGE)

After you enter a formula, the cell displays the result. The formula itself appears in the formula bar when you select the cell.

Following is an example:

1. Click in cell B5, which is where you want the result

(6)

5 | P a g e

Operators used in formulas

Operators are symbols that indicate the type of mathematical operation you want the formula to perform. Below is a list of operators that Excel recognizes.

Operator Name + Addition - Subtraction * Multiplication / Division ^ Exponentiation & Concatenation = Equal to > Greater than < Less than

>= Greater than or Equal to <= Less than or Equal to <> Not Equal to

When Excel calculates the value of a formula, it uses certain rules to determine the order in which the various parts of the formula are calculated. You need to understand these rules if you want your formulas to produce the desired results.

Below is a list of operator precedence.

Symbol Operator Precedence

^ Exponentiation 1

* and / Multiplication and Division 2 + and - Addition and Subtraction 3

& Concatenation 4

= Equal to 5

< Less than 5

> Greater than 5

You can use parentheses to override the Excel’s built-in order of precedence. Expressions within parentheses are always evaluated first.

The following formula uses parentheses to control the order in which the calculations occur. In

this case, cell B3 is subtracted from cell B2 and the result is multiplied by cell B4:

=(B2-B3)*B4 Answer: 200

If you enter the formula without the parentheses, Excel computes a different answer. Because

multiplication has a higher precedence, cell B3 is multiplied by cell B4. Then this result is

subtracted from cell B2. The formula without parentheses looks like this:

(7)

6 | P a g e

It’s a good idea to use parentheses even when they aren’t strictly necessary. Doing so helps to

clarify what the formula is intended to do.

For example, the following formula makes it perfectly clear that B3 should be multiplied by B4,

and the result subtracted from cell B2. Without the parentheses, you would need to remember

Excel’s order of precedence.

=B2-(B3*B4) Answer: -750

Every left parenthesis, of course, must have a matching right parenthesis. In some cases, if your formula contains mismatched parentheses, Excel may propose a correction to your formula. Please make sure it is correct.

Using Cell References in Formulas

Most formulas you create include references to cells or range. These references enable your formulas to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed values.

For example, if your formula refers to cell A1 and you change the value contained in A1, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.

When you use a cell (or range) reference in a formula, you can use three types of references:

 Relative - The row and column references can change when you copy the formula to another cell.

 Absolute - The row and column references do not change when you copy the formula because the reference is to an actual cell address.

 Mixed - Either the row or column reference is relative, and the other is absolute.

By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell.

When you create a formula by pointing to a cell, all cells and range references are relative. To change a reference to an absolute reference or mixed reference, you must do so manually by adding the dollar signs or if you select all or part of a formula, you can press F4 key to cycle range reference between the 4 styles.

Shortcut Key: F4 Key

For example, if you enter =C1 to start a formula, stay in cell C1 and pressing F4 converts the cell

reference to =$C$1. Pressing F4 again converts it to =C$1. Pressing it again displays =$C1. Pressing it one more time returns to the original =C1. Keep pressing F4 until Excel displays the type of reference that you want.

(8)

7 | P a g e

Absolute Reference

An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $G$1). Excel also allows mixed references in which only one of the address parts is absolute (for example, $C2).

1. Let’s calculate the ISF Charge

2. Click in cell D2, which is where you want the result

3. Enter the following formula: =$C2*$G$1 and press the enter key to complete the entry

Mixed Reference

Notice that both cell references are mixed. The reference to cell B2 uses an absolute reference for the column ($B), and the reference to cell C1 uses an absolute reference for the row ($1). As a result, this formula can be copied down and across, and the calculations will be correct.

1. Now Let’s calculate an increase to the insurance 2. Click in cell C2, which is where you want the result

(9)

8 | P a g e

Correcting Common Formula Errors

Sometimes, when you enter a formula, Excel displays a value that begins with a hash mark (#). This is a signal that the formula is returning an error value. You have to correct the formula (or correct a cell that the formula references) to get rid of the error display.

Tip:If the entire cell is filled with hash-mark characters, this means that the column isn’t wide enough to display the value. You can either widen the column or change the number format of the cell.

Excel Error Values

Error Value Explanation

#DIV/0! The formula is trying to divide by zero. This also occurs when the formula attempts to divide by what’s in a cell that is empty (that is, by nothing).

#NAME? The formula uses a name that Excel doesn’t recognize. This can happen if you delete a name that’s used in the formula or if you have unmatched quotes when using text. #N/A The formula is referring (directly or indirectly) to a cell that uses the NA function to signal

that data is not available. Some functions (for example, VLOOKUP) can also return #N/A. #NULL! The formula uses an intersection of two ranges that don’t intersect.

#NUM! A problem with a value exists; for example, you specified a negative number where a positive number is expected.

#REF! The formula refers to a cell that isn’t valid. This can happen if the cell has been deleted from the worksheet.

#VALUE! The formula includes an argument or operand of the wrong type. An operand is a value or cell reference that a formula uses to calculate a result.

A circular reference occurs when a formula refers to its own value – either directly or indirectly. For example, you create a circular reference if you enter =A1+A2+A3 into cell A3 because the formula in cell A3 refers to cell A3.

Excel displays a message in the left side of the status bar to remind you that a circular reference exists.

IFERROR Function

The Excel IFERROR function checks a formula (or expression) and returns the value of formula if there is no error otherwise it returns a value you specify.

Syntax: IFERROR (value, value_if_error)

 Value - The argument that is checked for an error

 Value_if_error – The value to return if the formula evaluates to an error

The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. In this example let’s divide cell A2 by B2. What is your result? If your result is an error, this IFERROR function would have helped.

1. Click in cell C2, which is where you want the result

(10)

9 | P a g e

Now let’s change the formula to include the IFERROR function. 1. Click in cell C2, which is where you want the result

2. Enter the following formula: =IFERROR(A2/B2,0) and press the enter key to complete the entry

ISERROR Function

This ISERROR function can be used to check for error values and returns TRUE or FALSE depending on the outcome.

Syntax: ISERROR (value)

 Value – The value that you want tested

You can use an IS function to get information about a value before performing a calculation or other action with it.

1. Click in cell C2, which is where you want the result

(11)

10 | P a g e

For example, you can use the ISERROR function in conjunction with the IF function to perform a different action if an error occurs: =IF(ISERROR(A2/B2),0,A2/B2)

This formula checks to see if an error condition exists with A2/B2. If so, the IF function returns the 0. If no error exists, the IF function performs the calculation A2/B2.

Joining two or more cells

Excel has a CONCATENATE function, which simply combines two or more cells. Also, Excel uses an ampersand (&) as its concatenation operator.

CONCATENATE Function

Let’s say you want to combine the first, middle and last name in one cell. 1. Click in cell D2, which is where you want the combined name

2. Enter the following formula: =CONCATENATE(B2,” “,C2,” “,A2) and press the enter key to complete the entry

(12)

11 | P a g e

You also can join two or more cell by using the ampersand (&). Following is an example. 1. Click in cell E2, where you want the other combined name

2. Enter the following formula: =(B2&” “&C2&” “&A2) 3. Press the enter key to complete the entry

TRIM Function

Often data imported into an Excel worksheet contains excess spaces. The TRIM function removes all leading and trailing excess spaces.

1. Click in cell D2, enter the following formula: =TRIM(CONCATENATE(B2,” “,C2,” “,A2)) 2. Press the enter key to complete the entry

(13)

12 | P a g e

Changing the case of text

Excel provides three handy functions to change the case of text.  UPPER - converts the text to all uppercase

 LOWER – converts the text to all lowercase

 PROPER – converts the text to proper case (the first letter in each word is capitalized, as in a proper name)

Note: These functions operate only on alphabetic characters; they simply ignore all other characters and

return them unchanged.

UPPER Function

This formula that follows, for example, converts the text in cell A2 to upper case.

1. Click in cell B2 and enter the following formula: =UPPER (A2) then press the enter key to complete the entry.

LOWER Function

This formula converts the text in cell A2 to lower case.

1. Click in cell C2 and enter the following formula: =LOWER(A2) 2. Press the enter key to complete the entry

(14)

13 | P a g e

PROPER Function

The PROPER function converts the text in cell B2 to proper case.

1. Click in cell D2 and enter the following formula: =PROPER(B2) then press the enter key to complete the entry

Extracting characters from a string

Excel users often need to extract characters from a string. For example, you may have a list of account lines and want to extract each section of the account line from each cell. Excel provides functions for extracting characters:

 LEFT - returns a specified number of characters from the beginning of a string.

 MID - returns a specified number of characters beginning at any position within a string.  RIGHT - returns a specified number of characters from the end of a string.

Those strings contained eight distinct parts:

1. The first three characters represent the fund code. 2. The second four digits represent the resource code. 3. The third one digit represents the project year. 4. The forth four digits represent the goal code. 5. The fifth four digits represent the function code. 6. The sixth four digits represent the object code. 7. The seventh two digits represent the site code. 8. The final four digits represent the department code.

LEFT Function

This formula that follows returns the leftmost characters from the beginning of a string, based on the number of characters you specify.

(15)

14 | P a g e

 Text - The text string that contains the characters you want to extract.  Num_chars - Specifies the number of characters you want left to extract For this example let’s extract the first three characters of the text entries.

1. Click in cell C3 and enter the following formula: =LEFT($A3,3), and press the enter key to complete the entry

Note: The num_chars must be greater than or equal to zero. If the num_chars is greater than the length

of text, LEFT returns all of text. Also if the num_chars is omitted, it is assumed to be 1.

MID Function

This MID function extracts the characters beginning at any position within a string, starting at the position you specify. Let’s extract the resource four characters in the string.

Syntax: MID (text, start_num, num_chars)

 Text –The text string containing the characters you want to extract  Start_num –The position of the first character you want to extract in text

 Num_chars – Specifies the number of characters you want MID to return from text

1. Now let’s click in cell D3 and enter the following formula: =MID ($A3,5,4) and press the enter key to complete the entry

Note: If the start_num is greater than the length of text, MID returns empty text. Also if the start_num is

(16)

15 | P a g e

RIGHT Function

The RIGHT function returns the last character or characters in a text string, based on the number of characters you specify.

Syntax: RIGHT (text, num_chars)

 Text - The text string containing the characters you want to extract.

 Num_chars - Specifies the number of characters you want from the right to extract. For this example let’s extract the department code.

1. Click in cell J3 and enter the following formula: =RIGHT($A3,4) and press the enter key to complete the entry

Note: The num_chars must be greater than or equal to zero. If the num_chars is greater than the length

of text, RIGHT returns all of text. Also if num_chars is omitted, it is assumed to be 1.

VALUE Function

Converts a text string that represents a number to a number

Syntax: VALUE (text)

 Text - The text reference to a cell containing the text you want to convert to a number. In this example let’s extract the resource code.

1. Click in cell H3 and enter the following formula: =VALUE(RIGHT(LEFT($A3,25),4)) and press the enter key to complete the entry

(17)

16 | P a g e

Working with Dates and Times

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

To help you perform complex date and time calculations, Excel includes many build-in date and time functions.

Function Description

DATE Returns the serial number of a particular date

DATEVALUE Converts a date in the form of text to a serial number

DAY Converts a serial number to a day of the month

DAYS360 Calculates the number of days between two dates based on a 360-day year

EDATE* Returns the serial number of the date that represents the indicated number of months before or after the start date

EOMONTH* Returns the serial number of the last day of the month before or after a specified number of months

MONTH Converts a serial number to a month

NETWORKDAYS* Returns the number of whole work days between two dates NOW Returns the serial number of the current date and time

TODAY Returns the serial number of today’s date

WEEKDAY Converts a serial number to a day of the week

WEEKNUM* Returns the week number in the year

WORKDAY* Returns the serial number of the date before or after a specified number of workdays

YEAR Converts a serial number to a year

YEARFRAC* Returns the year fraction representing the number of whole days between start_date and end_date

* In versions prior to Excel 2007, these functions are available only when the Analysis ToolPak add-in is installed.

NOW Function

The NOW function returns the current system date and time. This function will refresh the date/time whenever the worksheet recalculates.

(18)

17 | P a g e

TODAY Function

The TODAY function will return the current date based on your system clock and does not include the time.

1. Click in cell D1 and type in the following formula: =TODAY() Example:

YEARFRAC Function

The YEARFRAC function calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date).

Syntax: YEARFRAC (start_date, end_date,basis)

 Start_date – is a date that represents the start date  End_date – is a date that represents the end date  Basis – is the type of day count basis to use

Basis Day Count Basis 0 or omitted US (NASD) 30/360

1 Actual/actual

2 Actual/360

3 Actual/365

4 European 30/360

In our example, we have an employee with a hire date 01/01/07 and there last day is 07/30/07, so it give you a fraction of the year of .575342466.

1. Click in cell D2 and enter the following formula: =YEARFRAC(A2,B2,C2) and press the enter key to complete the entry

(19)

18 | P a g e

DATEDIF Function

The DATEDIF function returns the difference between two date values, based on the interval specified.

Syntax: DATEDIF (start_date, end_date, interval)

 Date1 is the first date  Date2 is the second date

 Interval is the interval type to return

The start_date and the end_date are the two dates to calculate the difference between. The interval is the interval of time to use to calculate the difference between Date1 and Date2.

If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.

The following table displays valid interval for the third argument. (If you are including the interval string directly within the formula, you must enclose it in double quotes. Otherwise if it is in another cell referenced by the formula, that cell should not have quotes around the interval string).

Interval Meaning Explanation

“y” Years The number of complete years. “m” Months The number of complete months.

“d” Days The number of days.

“md” Days Excluding Years and Months

The difference between the days (months and years are ignored). “ym” Months Excluding Years The difference between the months (days and years are ignored).

“yd” Days Excluding Years The difference between the days (years are ignored).

If Interval is not one of the items listed in above, DATEDIF will return a #NUM error. Let’s see how many months this employee has with us.

1. Click in cell D2 and type in the following formula: =DATEDIF($A2,$B2,$C2) and press the enter key to complete the entry

(20)

19 | P a g e

MONTH Function

The MONTH function returns the month (a number from 1 to 12) given a date value.

Syntax: MONTH (serial_number)

 Serial_number - The date of the month you are trying to find. Date should be entered by using the DATE function, or as results of other formulas or functions.

In this example we want the month our employee started working.

1. Click in cell B2 and type in the following formula: =MONTH($A2) and press the enter key to complete the entry

DAY Function

The DAY function returns the day of the month ( a number from 1 to 31) given a date value.

Syntax: DAY (serial_number)

 Serial_number - The date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions.

In this example we want the day the employee started working.

1. Click in cell B2 and type in the following formula: =DAY($A2) and press the enter key to complete the entry

(21)

20 | P a g e

YEAR Function

This YEAR function returns a four-digit year ( a number from 1900 to 9999) given a date value.

Syntax: YEAR (serial_number)

 Serial_number - The date of the year you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions.

This example we want the year the employee started work.

1. Click in cell B2 and type in the following formula: =YEAR($A2) and press the enter key to complete the entry

EDATE Function

The Excel EDATE function returns a date that is a specified number of months before or after a supplied start date.

Syntax: EDATE (start_date, months)

 Start_date – The initial date, from which to count the number of months  Months – The number of months to add to ( or subtract from) the start_date

Note: Microsoft advises that you do not type dates directly into functions, because Excel interprets text

representations of dates differently, depending on the date interpretation settings on your computer.

Therefore the start_date argument for the EDATE function should be input as either: A reference to a cell containing a date or a date returned from another function or formula

Let’s calculate when the depreciation ends on the following items.

1. Click in cell C2 and type in the following formula: =EDATE($B2,(12*$D2) and press the enter key to complete the entry

(22)

21 | P a g e

EOMONTH Function

The EOMONTH function returns the last day of the month that is a specified number of months before or after an initial supplied start date.

Syntax: EOMONTH (start_date, months)

 Start_date – The initial date

 Months – The number of months to add to (or subtract from) the start_date, before returning the last day of the resulting month

Note: When supplying a date to a function, this should be input as either: A reference to a cell

containing a date or a date returned from another function or formula.

Now let’s calculate the due date of a project that falls at the end of the month.

1. Click in cell B3 and type in the following formula: =EOMONTH($B$1,B2) and press the enter key to complete the entry

(23)

22 | P a g e

Creating Formulas that Count and Sum

Generally, a counting formula returns the number of cells in a specified range that meet certain criteria. A summing formula returns the sum of the values of the cells in a range that meet certain criteria.

COUNT Returns the number of cells that contain a numeric value

COUNTA Returns the number of nonblank cells

COUNTBLANK Returns the number of blank cells

COUNTIF Returns the number of cells that meet a specified criterion

COUNTIFS* Returns the number of cells that meet multiple criteria

DCOUNT Counts the number of records that meet specified criteria; used with a worksheet database.

DCOUNTA Counts the number of nonblank records that meet specified criteria; used with a worksheet database.

DEVSQ Returns the sum of squares of deviations of data points from the sample mean; used primarily in statistical formulas

DSUM Returns the sum of a column of values that meet specified criteria; used with a worksheet database.

FREQUENCY Calculates how often values occur within a range of values and returns a vertical array of numbers. Used only in a multi-cell array formula,

SUBTOTAL When used with a first argument of 2, 3, 102, or 103, returns a count of cells that comprise a subtotal; when used with a first argument of 9 or 109, returns the sum of cells that comprise a subtotal

SUM Returns the sum of its arguments

SUMIF Returns the sum of cells that meet a specified criterion

SUMIFS* Returns the sum of cells that meet multiple criteria

SUMPRODUCT Multiplies corresponding cells in two or more ranges and returns the sum of those

products

SUMSQ Returns the sum of the squares of its arguments; used primarily in statistical formulas

SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two ranges; used primarily in statistical formulas

SUMXMY2 Returns the sum of squares of the differences of corresponding values in two ranges; used primarily in statistical formulas

SUMX2MY2 Returns the sum of the differences of squares of corresponding values in two ranges; used primarily in statistical formulas

(24)

23 | P a g e

Quick Count or Sum

Excel’s status bar can display useful information about the currently selected cells—no formulas required. Normally, the status bar displays the sum and count of the values in the selected range. However, you can click on the status bar on the bottom of your screen, right-click to bring up a menu with other options.

You can choose any or all of the following:  Average  Count  Numerical Count  Minimum  Maximum  Sum Example:

COUNT Function

This COUNT function returns the number of cells that contain a numeric value.

Syntax: COUNT (value1, value2)

 Value1, Value2 - The arguments (e.g. value1) can be cell references, or values typed into the COUNT formula.

Let’s count the number employee’s we have on insurance.

1. Click in cell F1 and type in the following formula: =COUNT(A2:A13) and press the enter key to complete the entry

(25)

24 | P a g e

COUNTA Function

The COUNTA function will count cells that are not empty.

Syntax: COUNTA (value1, value2)

 Value1, Value2 - The arguments (e.g. value1) can be cell references, or values typed into the COUNTA formula.

Now let’s count the employee with insurance by their plan.

1. Click in cell F1 and type in the following formula: =COUNTA(C2:C14) and press the enter key to complete the entry

(26)

25 | P a g e

COUNTBLANK Function

The COUNTBLANK function returns the number of blank (empty) cells in a range.

Syntax: COUNTBLANK (range)

 Range - The range from which you want to count the blank cells. Let’s count the blank rows.

1. Click in cell F1 and type in the following formula: =COUNTBLANK(C2:C14) and press the enter key to complete the entry

Note: Cells with formulas that return “” (empty text) are also counted. Cells with zero values are not

counted.

COUNTIF Function

In Excel, count cells that meet a specific criterion.

Syntax: COUNTIF (range, criteria)

 Range – The range of cells from which you want to count cells

 Criteria – The criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted

Now let’s count the employees that have COMP insurance coverage.

1. Click in cell F1 and type in the following formula: =COUNTIF(C2:C14,”COMP”) and press the enter key to complete the entry

(27)

26 | P a g e

COUNTIFS Function

In Excel 2007 and later versions, you can use the COUNTIFS function to count rows that meet two or more criteria.

Syntax: COUNTIFS (criteria_range1, criteria1, criteria_range2, criteria2)

 Criteria_range1 – The first range in which to evaluate the associated criteria

 Criteria1 – The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted

 Criteria_range, criteria2 – Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed

Let’s count the employee with the F code and with the COMP plan.

1. Click in cell F1 and type in the following formula: =COUNTIFS(B2:B14,”F”,C2:C14,”COMP”) and press the enter key to complete the entry

(28)

27 | P a g e

SUM Function

The SUM Function provides a quick way to sum columns or rows of numbers in an Excel worksheet.

Note: You can enter up to 255 numbers into this function

Let’s add up all July expenditures.

1. Click in cell B5, which is where we want the formula to sum up the column 2. Enter the following formula: =SUM(B2+B3+B4)

(29)

28 | P a g e

Quick AutoSum

1. Click in the blank cell below the cells that you want to sum, cell C5

2. Press the AutoSum button on the Ribbon’s Home tab on the editing section of the toolbar 3. Now press the Enter key to complete the entry

Note: Instead of using the AutoSum button, you also can use the Keyboard Shortcut: Alt + =

Grand Total a range of Cells

You can calculate row totals, column totals, and a grand total for a range of cell, all in one step. 1. Highlight the range of cells, and the blank row below the range, and the blank cells in the

column to the right. Example: Cells B2:H5

2. Click the Keyboard Shortcut: Alt + = 3. Below is an example of your results

(30)

29 | P a g e

ROUNDUP Function

The ROUNDUP function returns a number rounded up to a specified number of digits, away from 0.

Syntax: ROUNDUP (number, num_digits)

 Number – The number that you want rounded up

 Num_digits – The number of digits to round the number up to Let’s roundup the total to the zero decimals.

1. Click in cell B7, which is where we want your result 2. Enter the following formula: =ROUNDUP(SUM(B2:B4),0) 3. Press the Enter key to complete the entry

(31)

30 | P a g e

ROUNDDOWN Function

The ROUNDDOWN function returns a number rounded down to a specified number of digits. Always rounds toward 0.

Syntax: ROUNDDOWN (number, num_digits)

 Number – Any real number that you want rounded down

 Num_digits – The number of digits to round the number down to Let’s round down the total to the 0 decimals.

1. Click in cell B7, which is where we want your result

2. Enter the following formula: =ROUNDDOWN(SUM(B2:B4),0) 3. Press the Enter key to complete the entry

MROUND Function

The MROUND function can be used to round a number upwards or downwards to a specified multiple.

Syntax: MROUND (number, multiple)

 Number - The value to rounded

 Multiple - The function rounds the number specified above up or down to the nearest multiple of this value. The number is rounded up if the last digit is greater than or equal to 5. If it is less than 5, the number is rounded down.

For example, the MROUND function can be used to round up or down the costs of items to the nearest dime ($ 0.10 ) to avoid having to deal with smaller change such as pennies ($ 0.01 ) and nickels ($ 0.05 ).

1. Click in cell B7, which is where we want your result 2. Enter the following formula: =MROUND(SUM(B2:B4),0.1) 3. Press the Enter key to complete the entry

(32)

31 | P a g e

CEILING Function

The CEILING function is used to round a number upwards to the nearest multiple of a specified value.

Syntax: CEILING (number, significance)

 Number – The value you want to round

 Significance – The multiple to which you want to round

Note: If either argument is nonnumeric, CEILING returns the #VALUE! error value.

For example, the function can be used to round up the costs of items to the nearest dime ( $ 0.10 ) to avoid having to deal with smaller change such as pennies ($ 0.01 ) and nickels ($ 0.05 ).

1. Click in cell B7, which is where we want your result 2. Enter the following formula: =CEILING(SUM(B2:B4),0.1) 3. Press the Enter key to complete the entry

(33)

32 | P a g e

MOD Function

The MOD Function returns the remainder after a number is divided by a divisor.

Syntax: MOD (number, divisor)

 Number – The number for which you want to find the remainder  Divisor – The number by which you want to divide the number

Note: If the divisor is 0, MOD returns the #DIV/0! Error value.

Let’s calculate the remainder portion of a division operation. 1. Click in cell A3, which is where we want your results 2. Enter the following formula: =MOD(A1,A2)

3. Press the Enter key to complete the entry

SUMIF Function

The SUMIF formula/function adds all numbers in a range of cells, based on a given criteria.

As an example the resource number. The SUMIF() function will look for that specific resource number and then sum the requested column.

Syntax: SUMIF(range, criteria, sum_range)

 Range – Range of cells that you want to apply the criteria against  Criteria – Used to determine which cells to add

 Sum_Range – The cells to sum

Now let’s sum up all unrestricted resources less than or equal to 1999.

1. Click in cell L5, which is where we want the formula to sum up the column 2. Enter the following formula: =SUMIF($C2:$C8,”<=1999”,$J2:$J8)

(34)

33 | P a g e

SUMIFS Function

New in Excel 2007 is the SUMIFS() function, which allows addition of multiple range/criteria pairs. In this example we set two conditions using SUMIFS, resources less than or equal to 1999 and object 8677.

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

 sum_range – Required one or more cells to sum, including numbers or names, ranges, or cell references that contain numbers. Blank and text values are ignored.

 criteria_range1 – Required the first range in which to evaluate the associated criteria.  criteria1 - Required the criteria in the form of a number, expression, cell reference, or

text that defines which cells in the criteria_range1 argument will be added.

 criteria_range2, criteria2, … - Optional additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

1. Click in cell L2, which is where we want the formula to sum up the column

2. Enter the formula: =SUMIFS($J$2:$J$8,$C$2:$C$8,”<=1999”,$G$2:$G$8,”=8677”) 3. Press the Enter key to complete the entry

You can see it adds the total revenue that is unrestricted (resource less than or equal to 1999) and were revenue (object is equal to 8677).

(35)

34 | P a g e

SUMProduct Function

The SUMProduct function multiples the corresponding items in the arrays and returns the sum of the results.

Syntax: SUMProduct(array1, array2, …)

 array1, array2, … - Are the ranges of cells or arrays that you wish to multiply.

Note: All arrays must have the same number of rows and columns. You must enter at least 2 arrays and

you can have up to 30 arrays. SUMProduct treats array entries that are not numeric as if they were zeros.

For example, we want to consider only the Alt Ed department values:

1. Click in cell I2, which is where we want the formula to sum up the column 2. Enter the formula: =SUMPRODUCT((C2:C8)*(D2:D8)*(A2:A8=”Alt Ed”)) 3. Press the Enter key to complete the entry

(36)

35 | P a g e

SUBTOTAL Function

The SUBTOTAL Function returns the subtotal of the numbers in a column in a list or a database.

Syntax: SUBTOTAL (function_num, ref1, ref2)

 Function_num – The number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list

 Ref1, Ref2 – Ranges or references for which you want the subtotal

Function_Num (Includes Hidden Values) Function_Num (Ignores Hidden Values) Function 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP

Let’s calculate the subtotal for April expenditures.

1. Click in cell B9, which is where we want the formula to subtotal up the column 2. Enter the following formula: =SUBTOTAL(9,B2:B8)

(37)

36 | P a g e

AVERAGE Function

The AVERAGE function returns the average (arithmetic mean) of the numbers provided.

Syntax: AVERAGE (nuber1, number2)

 Number1 – The first number, cell reference, or range for which you want to average  Number2 – Additional numbers, cell references or ranges for which you want the average Let’s average the department’s expenditures.

1. Click in cell B7, which is where we want the formula to average up the column 2. Enter the following formula: =AVERAGE($B2:$B6)

3. Press the Enter key to complete the entry

MAX Function

The MAX function returns the largest value from the numbers provided.

Syntax: MAX (number1, number2)

 Number1, Number2 – The numbers for which you want to find the maximum value Now let’s show what department has the most expenditure’s.

1. Click in cell B7, which is where we want the formula to show the maximum for the column 2. Enter the following formula: =MAX(B2:B6)

(38)

37 | P a g e

MIN Function

The MIN function returns the smallest value from the numbers provided.

Syntax: MIN (number1, number2)

 Number1, Number2 – The numbers for which you want to find the minimum value Now let’s show what department has the least expenditure’s.

1. Click in cell B7, which is where we want the formula to show the minimum for the column 2. Enter the following formula: =MIN(B2:B6) and press the Enter key to complete the entry

Information Functions

LEN Function

The LEN Function in Excel counts the characters in a string and returns that number.

1. Click in cell B1 and type in the following formula: =LEN(A1) and press the enter key to complete the entry

(39)

38 | P a g e

Row Function

The ROW function returns the row number of a cell reference.

Syntax: ROW (reference)

 Reference – is the cell or range of cells for which you want the row number.

Note: If reference is omitted, it assumed to be the reference of the cell in which the ROW function

appears.

1. Click in cell A2 and type in the following formula: =ROW(A2) and press the enter key to complete the entry

For example, the formula =ROW (A2) returns a 2, because row 2 is the second row.

ROWS Function

The ROWS function takes an Excel range and returns the number of rows that are contained within the range.

Syntax: ROWS (array)

 Array – An array, an array formula, or a reference to a range of cells for which you want the number of rows.

1. Click in cell A2 and type in the following formula: =ROWS(A3:A7) and press the enter key to complete the entry

(40)

39 | P a g e

COLUMN Function

The Column function returns the column number of a cell reference.

Syntax: COLUMN (reference)

 Reference – It is a reference to a cell or range of cells.

Note: If the reference parameter is omitted, then the Column function assumes that the reference is the

cell address in which the Column function has been entered in.

1. Click in cell C1 and type in the following formula: =COLUMN(C1) and press the enter key to complete the entry

For example, the formula =COLUMN (C1) returns a 3, because column C is the third column.

COLUMNS Function

The COLUMNS function returns the number of columns that are contained with the range.

Syntax: COLUMNS (reference)

 Reference – The range of cells or an array

1. Click in cell A3 and type in the following formula: =COLUMNS(A5:D5) and press the enter key to complete the entry

(41)

40 | P a g e

ISBLANK Function

The ISBLANK function checks to see if a certain cell is empty or not. If the cell is empty, a value of TRUE is returned by the function, otherwise it returns FALSE.

Syntax: ISBLANK (value)

 Value - Is the value you want tested

Let’s check to see if cell A1 and A2 are empty.

1. Click in cell B2 and type in the following formula: =ISBLANK(A2) and press the enter key to complete the entry

ISNUMBER

The ISNUMBER function determines if the data in a certain cell is a number or not. If the data is a number, a value of TRUE is returned by the function.

If it is not a number, or the cell is empty, a FALSE value is returned. Note: For this function dates and times are considered a numbers.

Syntax: ISNUMBER (value)

 Value – Refers to the cell reference that is being checked.

Now let’s check to see if the data in cell A1, A2 and A3 is a number or not.

1. Click in cell B1 and type in the following formula: =ISNUMBER(A1) and press the enter key to complete the entry

(42)

41 | P a g e

Lookup Formulas

A lookup formula essentially returns a value from a table by looking up another related value. A

common telephone directory provides a good analogy. If you want to find a person’s telephone number, you first locate the name (look it up) and then retrieve the corresponding number.

Functions Relevant to Lookups

Several Excel functions are useful when writing formulas to look up information in the following list and describe these functions.

CHOOSE Returns a specific value from a list of values (up to 29) supplied as arguments.

HLOOKUP Horizontal lookup. Searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table.

IF Returns one value if a condition you specify is TRUE, and returns another value if the condition is FALSE.

IFERROR* If the first argument returns an error, the second argument is evaluated and returned.

INDEX Returns a value (or the reference to a value) from within a table or range.

LOOKUP Returns a value either from a one-row or one-column range. Another form of the LOOKUP function works like VLOOKUP but is restricted to returning a value from the last column of a range.

MATCH Returns the relative position of an item in a range that matches a specified value.

OFFSET Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

VLOOKUP Vertical lookup. Searches for a value in the first column of a table and returns a value in the same row from a column you specify in the table.

* Available in Excel 2007 only.

IF Function

The IF function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

Syntax: IF (logical_text, value_if_ true, value_if_false)

 Logical_test – Is any value or expression that can be evaluated to TRUE or FALSE

 Value_if_true – Is the value that is returned if logical_text is TRUE

 Value_if_false – Is the value that is returned if logical_test is FALSE

Now let’s calculate employees with a class of M will receive a stipend of $1,440. 1. Click in cell D2 this is where you want the result

2. Enter the following formula: =IF($B2=”M”,1440,0) 3. Press the Enter key to complete the entry

(43)

42 | P a g e

AND Function

The AND function returns TRUE if all conditions are TRUE; returns FALSE if any of the conditions are FALSE.

Syntax: AND (logical1, logical2)

 Logical1 – The first condition that you want to test that can evaluate to either TRUE or FALSE  Logical2 – Additional conditions that you want to test that can evaluate to either TRUE or FALSE Let’s calculate employees that are a class M and are Full-Time will get a stipend of $1,440.

1. Click in cell D2 this is where you want the result

2. Enter the following formula: =IF(AND($B2=”M”,$C2=”Full-Time”),1440,0) 3. Press the Enter key to complete the entry

(44)

43 | P a g e

OR Function

The OR function returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE.

Syntax: OR (logical1, logical2)

 Logical1 – The first condition that you want to test that can evaluate to either TRUE or FALSE  Logical2 – Additional conditions that you want to test that can evaluate to either TRUE or FALSE Now let’s calculate employees that are class C or a status of Full-Time will get a stipend of $1,440.

1. Click in cell D2 this is where you want the result

2. Enter the following formula: =IF(OR($B2=”C”,$C2=”Full-Time”),1440,0) 3. Press the Enter key to complete the entry

VLOOKUP Function

The VLOOKUP function looks up the value in the first column of the lookup table and returns the value in the same row from another column in the table. The lookup table is arranged vertically (which explains the V in the function’s name).

Syntax: VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)

 Lookup_value – The value to search in the first column of the table  Table_array – The range that contains the lookup table

 Col_index_num – The column number within the table from which the matching value is returned.

 Range_lookup – Enter FALSE to find an exact Match based on your value or enter TRUE to find and approximate match based on your value, which will look for the next largest value that is less than the value

The department table is commonly used in Business. The department table shows the department number with the department name.

(45)

44 | P a g e

In this example let’s lookup the department name: 1. Click in cell J2 this is where you want the result

2. Enter the following formula: =VLOOKUP($I2,$M$2:$N$11,2,TRUE) 3. Press the Enter key to complete the entry

HLOOKUP Function

The HLOOKUP function searches for value in the top row of a table and returns the value in the same column based on the row_index_num.

Syntax: HLOOKUP (lookup_value,table_array,row_index_num,range_lookup)

 Lookup_value – The value to search for in the first row of the table

 Table_array - The range that contains the lookup table, which is sorted in ascending order  Row_index_num – The row number in the table from which the matching value must be

returned

 Range_lookup – Enter FALSE to find an exact Match based on your value or enter TRUE to find and approximate match based on your value, which will look for the next largest value that is less than the value

You will notice we setup an Ins Table which has the Ins Plan and the cost for each plan. Now in this example let’s find the employee cost of insurance.

1. Click in cell C3 this is where you want the result

2. Enter the following formula: =HLOOKUP($B3,$E$2:$H$3,2,FALSE) 3. Press the Enter key to complete the entry

(46)

45 | P a g e

MATCH Function

The MATCH function returns the relative position of a cell in a range that matches a specified value.

Syntax: MATCH (lookup_value, lookup_array,match_type)

 Lookup_value – The value you want to match in lookup_array  Lookup_array – The range being searched

 Match_type – An integer (-1,0, or 1) that is specifies how the match is determined match_type Explanation

1 (default)

The MATCH function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.

If the match_type parameter is omitted. The MATCH function assumes a match_type of 1 0 The MATCH function will find the first value that is equal to value. The array can be sorted

in any order

-1 The MATCH function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order

In our example let’s see if the ID’s match.

1. Click in cell E2 this is where you want the result

2. Enter the following formula: =MATCH($D2,$B$2:$B$5,0) 3. Press the Enter key to complete the entry

(47)

46 | P a g e

INDEX Function

The INDEX function returns a cell from a range. You should notice that the data range returns you the value in the intersection of the (row number) and the (column number)

Syntax: INDEX (array,row_num,column_num)

 Array - A range of cells

 Row_num – A row number within a array  Column_num – A column number within array

In this example let’s find the amount this employee will receive in a range 3 and step 5.

1. Click in cell C11 and type in the following formula: =INDEX(B3:F5,C9,C10) and press the enter key to complete the entry

(48)

47 | P a g e

Combining the INDEX & MATCH functions

The MATCH function tells the INDEX function which row to look in. Let’s find this employee salary with a range 78 and step D.

1. Click in cell C12 this is where you want the result

2. Enter the following formula: =INDEX(A3:F6,MATCH(C10,A3:A6,0),MATCH(D10,A3:F3,0)) and press the Enter key to complete the entry

OFFSET Function

The OFFSET function returns the value of a cell that is a specified number of rows and columns away from a cell or range of cells that you referenced in an adjacent range.

Syntax: OFFSET (reference,rows,cols,height, width)

 Reference – the starting range from which the offset will be applied

 Rows – The number of rows to apply as the offset to the range. (This can be positive or negative numbers

 Cols – The number of columns to apply as the offset to the range. This can be a positive or negative number

 Height – (Optional) This is the number of rows that you want the retuned range to be  Width – (Optional) This is the number of columns that you want the returned range to be Enter the following formula into cell B7: =OFFSET (C2,2,-1)

(49)

48 | P a g e

Now let’s break this formula apart so you may understand it clearer.  C2 – Is the referenced cell

 2 – Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up

 -1 – Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left

When you use this formula, the OFFSET function returns the value of the cell that is located two rows down (2) and 1 row to the left (-1) of cell C2 (which is cell B4). The value in cell B4 is “5000”. Therefore, the formula returns “5000”.

(50)

49 | P a g e

Excel Formulas and Functions