Using Advanced Functions and Formulas.docx

# Using Advanced Functions and Formulas ## Lesson 5 This next formula uses the Average (number 1, number 2) function to find the average of given numbers. `[Average (A3:A5)]` Use the formula. ``` = Average (A3:A5) A3 = 365 A4 = 346 A5 = 474 Average = 395 ``` The following are examples of formula...

# Using Advanced Functions and Formulas ## Lesson 5 This next formula uses the Average (number 1, number 2) function to find the average of given numbers. `[Average (A3:A5)]` Use the formula. ``` = Average (A3:A5) A3 = 365 A4 = 346 A5 = 474 Average = 395 ``` The following are examples of formulas that you can explore in the MSExcel. | Function | Formula | | ---------------------------------------------- | ------------- | | To add the values in cells A1, A2, and A3 | =A1+A2+A3 | | To use the SQRT function to return the square root of the value in A1 | =SQRT(A1) | | To return to the current date | =TODAY() | | To convert the text "come here" to "COME HERE" by using the UPPER wordsheet function | =UPPER("come here") | | To test the cell A1 to find if it contains a value greater than 0 | =IF(A1>0) | ## Parts of a Formula A formula may contain any or all of the following: ``` =pi() *A^2 2 } A BCD ``` 1. **Functions** - The `pi()` function returns the value of pi, that is, 3.142. This is also a constant or a value that is not calculated; it always stays the same. 2. **Reference** - `A2` returns the value in cell A2 3. **Constant** - such as 2. This is a number or a text value that is entered directly into a formula, 4. **Operator** - The `^` (caret) operator raises a number to a power, while the `*` (asterisk) operator multiplies numbers. The operator expresses the type of operation that you would want to do to the elements of a formula. # Types of Operators There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference. ## 1. Arithmetic Operators These are used to execute basic mathematical operations such as addition, subtraction, multiplication or division; to combine numbers; and produce numeric results. | Arithmetic Operators | Function | | --------------------- | ------------------- | | + (plus sign) | Addition | | - (minus sign) | Subtraction or Negation | | * (asterisk) | Multiplication | | / (forward slash) | Division | | % (percent sign) | Percent | | ^ (caret) | Exponentiation | ## 2. Comparison Operators - When comparing two values using comparison operators the result is a logical value which is either true or false. | Comparison Operators | Function | | --------------------- | -------- | | = (equal sign) | A1=B1 | | > (greater than sign) | C1>D1 | | < (less than sign) | E1<F1 | | >= (greater than or equal to sign) | G1>=F1 | | <= (less than or equal to sign) | H1<=J1 | | <> (not equal sign) | K1<>L1 | ## 3. Text concatenation Operator To concatenate (join) one or more text strings to produce a single piece of text, use the ampersand (&). | Text Operator | Meaning | Example | | -------------- | -------------------------------------------- | ----------------------- | | & (ampersand) | Connects or concatenates two values to produce one continuous text value | "Home" & "work" results in Homework | ## 4. Reference operators These are used to combine ranges of cells for calculations with the following operators. | Reference Operator | Meaning | Example | | ------------------- | --------------------------------------------------------------------- | ---------- | | : (colon) | Range operator, which produces one reference to all the cells between two references, including the said two references | A12:A18 | # Operator Precedence | **Order** | **Operator** | **Meaning** | | ---------- | ------------- | ---------------------------------------- | | 1st | : (colon) | Reference operator | | 2nd | (single space) | | | 3rd | , (comma) | | | 4th | - | Negation (as in -2) | | 5th | % | Percent | | 6th | ^ | Exponentiation | | 7th | * and / | Multiplication and division | | 8th | + and - | Addition and subtraction | | 9th | & | Connects two strings of text or concatenation | | 10th | = < > <= >= <> | Comparison |