Spreadsheets Objective 6 02 Explain advanced spreadsheet concepts

Spreadsheets Objective 6. 02 Explain advanced spreadsheet concepts and functions Advanced Calculations

Using Formulas and Functions FORMULA ~ A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).

Using Formulas and Functions FUNCTION ~ Predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure.

Using Formulas Mathematical Operators § Operators specify the type of calculation that you want to perform on the elements of a formula. § There are four different types of calculation operators: arithmetic, comparison, text, and reference.

Using Formulas (Continued) Types of operators: Arithmetic operators ~ To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Using Formulas (Continued) Arithmetic operators § § + (plus sign) ~ Addition (3+3) – (minus sign) ~ Subtraction (3– 1) or Negation (– 1) * (asterisk) ~ Multiplication (3*3) / (forward slash) ~ Division (3/3) § % (percent sign) ~ Percent (20%) § ^ (caret) ~ Exponentiation (3^2)

Using Formulas (Continued) Comparison operators ~ You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.

Comparison operators § § = (equal sign) ~ Equal to (A 1=B 1) > (greater than sign) ~ Greater than (A 1>B 1) < (less than sign) ~ Less than (A 1<B 1) >= (greater than or equal to sign) ~ Greater than or equal to (A 1>=B 1) § <= (less than or equal to sign) ~ Less than or equal to (A 1<=B 1) § <> (not equal to sign) ~ Not equal to (A 1<>B 1)

Text Operators Use the ampersand (&) to connect, or concatenate, one or more text strings to produce a single piece of text. § The text items can be text strings, numbers, or single-cell references. § =CONCATENATE("Stream population for ", A 2, " ", A 3, " is ", A 4, "/mile") § Concatenates a sentence from the data above (Stream population for brook trout species is 32/mile)

Reference operators Combine ranges of cells for calculations with the following operators. § : (colon) ~ Range operator, which produces one reference to all the cells between two references, including the two references (B 5: B 15) § , (comma) ~ Union operator, which combines multiple references into one reference (SUM(B 5: B 15, D 5: D 15)) § (space) ~ Intersection operator, which produces on reference to cells common to the two references (B 7: D 7 C 6: C 8)

Order of Operations § Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. § Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. § Excel calculates the formula from left to right, according to a specific order for each operator in the formula. § If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table.

Order of Operations (Continued) § If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right. § To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. § For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result. § =5+2*3

Using Formulas (Continued) § In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21. § =(5+2)*3 § In the example below, the parentheses around the first part of the formula force Excel to calculate B 4+25 first and then divide the result by the sum of the values in cells D 5, E 5, and F 5. § =(B 4+25)/SUM(D 5: F 5)

B. USING FUNCTIONS § FUNCTIONS ~ § Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. § Functions can be used to perform simple or complex calculations.

B. USING FUNCTIONS § Sum ~ The sum of the values. This is the default function for numeric data. § Average ~ The average of the values. § Count ~ The number of data values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for data other than numbers. § Max ~ The largest value. § Min ~ The smallest value.

B. USING FUNCTIONS § If Statement ~ Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. § If Statement ~ Use IF to conduct conditional tests on values and formulas. § Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A 10=100 is a logical expression; if the value in cell A 10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

B. USING FUNCTIONS § If Statement ~ Use IF to conduct conditional tests on values and formulas. § Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula. § If Statement ~ Use IF to conduct conditional tests on values and formulas. § Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget".

B. USING FUNCTIONS § If Statement ~ Use IF to conduct conditional tests on values and formulas. § If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

B. USING FUNCTIONS § Lookup Tables ~ Returns a value either from a one-row or one-column range or from an array. § Array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument. § Lookup Tables ~ Returns a value either from a one-row or one-column range or from an vector. § A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.