03 Excel formulas and functions

03 Excel formulas and functions

• 1.
  Excel Formulas and Functions
• 2.
  Formulas Formulas are enteredin the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. See the example below to view the formula for calculating the sub total for a number of textbooks. The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.
• 3.
  Formula Operators There arefour basic Mathematical Operators when writing a formula. These operators are used to tell the formula what action to perform. The following table lists the operators, its symbol. Operation Symbol Symbol Name Addition + Plus Sign Subtraction - Dash or hyphen Multiplication * Asterisk Division / Forward slash The next table lists the order of operation for each mathematical operator. As you begin to write your formulas, keep in mind that information in parenthesis ( ) is always performed first while everything outside the parenthesis is performed left to right. Operator Operation Order of Calculation AND, OR, NOT Logic Test: AND, OR, NOT 1 + or - Positive or Negative Value 2 ^ Exponentiation 3 * or / Multiplication or Division 4 +- Addition or Subtraction 5 & Text Concatenation 6 Logic Test 7 = Equal to 7 <> Not Equal To 7 <= Less than or Equal to 7 >= Greater than or Equal to 7
• 4.
  Functions • Built-in Excel Functions can be a faster way of doing mathematical operations than formulas. • Example- if you wanted to add the values of cells D1 through D10, you could type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10". • A shorter way would be to use the SUM function and simply type "=SUM(D1:D10)". Function Example Description SUM =SUM(A1:A100) finds the sum of cells A1 through A100 AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10 MAX =MAX(C1:C100) returns the highest number from cells C1 through C100 MIN =MIN(D1:D100) returns the lowest number from cells D1 through D100 SQRT =SQRT(D10) finds the square root of the value in cell D10 TODAY =TODAY() returns the current date (leave the parentheses empty) SUM( ) function The SUM( ) function is probably the most common function in Excel. It adds a range of numbers. To build a SUM( ) function, begin by typing the = sign; all functions begin with the = sign. Next type the word SUM followed by an open parenthesis. You must now tell Excel which cells to sum. Using the mouse, click and drag over the range of cells you wish to add. A dotted outline will appear around the cells and the cell range will be displayed in the formula bar. When you have the correct cells selected, release the mouse button, type a closing parenthesis and press the <Enter> key. If you do not want to use the mouse, type in the references of the cells you want to sum. For example, to add cells B3 through B5, type =SUM(B3:B5). Excel interprets B3:B5 as the range of cells from B3 to B5.
• 5.
  Insert Function Excel has hundreds of prewritten formulas which make it easy to do complex procedures with numbers, dates, times, text, and more. •Type a brief description of what you want to do in the Search for a function box. In this example, you could type "mortgage payment" or some other keywords. •Click Go. Tips •You can also select a function category in the Or select a category box. This action will display a list of related functions, which you can then browse through. •If you'd like help on how to enter the arguments, you could type the function name in the Search for a function box and click OK. Click the Insert Function button on the formula bar. The Insert Function dialog box opens In the Search for a function box, type a description of what you want to do.
• 6.
  AutoSum AutoSum button In Excel,the editing group on the home tab has a button that simplifies adding a column or row of numbers. The AutoSum button, which resembles the Greek letter Sigma (shown above), automatically creates a SUM( ) function. When you click the AutoSum button Excel creates a sum function for the column of numbers directly above or the row of numbers to the left. Excel pastes the SUM( ) function and the range to sum into the formula bar. If the range is not correct, simply select the proper range with your mouse on the worksheet. When you have the correct range entered, press the <Enter> key to complete the function.
• 7.
  Autofilling Functions Autofill canalso be used to copy functions. In the example below, column A and column B each contain lists of numbers and column C contains the sums of columns A and B for each row. The function in cell C2 would be "=SUM(A2:B2)". This function can then be copied to the remaining cells of column C by activating cell C2 and dragging the handle down to fill in the remaining cells. The autofill feature will automatically update the row numbers as shown below if the cells are reference relatively
• 8.
  Cell Reference There aretwo basic types of cell references in Excel: relative and absolute. The difference between absolute and relative cell references becomes apparent when you copy formulas from one cell to another. When you copy a formula containing relative references, the references are adjusted to reflect the new location. Absolute references always refer to the same cell, regardless of where the formula is copied. Relative references are the default. To create an absolute reference, type $ before each part of the cell address.
• 9.
  Relative / Absolute Relative Absolute This shows the formulas used to create the order form below. We used the fill handle which usually gives us the relative reference. For the sales tax calculation we needed to use the absolute reference in cell C9 To toggle between seeing the formulas and seeing the results, hold down the Ctrl key and press the tilde ~