Type Formulas in Microsoft Excel

Revision as of 03:41, 12 January 2016 by Kipkis (Kipkis | contribs) (importing article from wikihow)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Microsoft Excel's power is in its ability to calculate and display results from data entered into its cells. To calculate anything in Excel, you need to enter formulas into its cells. Formulas can be simple arithmetical formulas or complicated formulas involving conditional statements and nested functions. All Excel formulas use a basic syntax, which is described in the steps below.

Steps

Excel Formula Syntax

  1. Begin every formula with an equal sign (=). The equal sign tells Excel that the string of characters you're entering into a cell is a mathematical formula. If you forget the equal sign, Excel will treat the entry as a character string.
  2. Use coordinate references for cells that contain the values used in your formula. While you can include numeric constants in your formulas, in most cases you'll use values entered in other cells (or the results of other formulas displayed in those cells) in your formulas. You refer to those cells with a coordinate reference of the row and column the cell is in. There are several formats:
    • The most common coordinate reference is to use the letter or letters representing the column followed by the number of the row the cell is in: A1 refers to the cell in Column A, Row 1. If you add rows above the referenced cell or columns above the referenced cell, the cell's reference will change to reflect its new position; adding a row above Cell A1 and a column to its left will change its reference to B2 in any formula the cell is referenced in.
    • A variation of this reference is to make row or column references absolute by preceding them with a dollar sign ($). While the reference name for Cell A1 will change if a row is added above or a column is added in front of it, Cell $A$1 will always refer to the cell in the upper left corner of the spreadsheet; thus, in a formula, Cell $A$1, could have a different, or even invalid, value in the formula if rows or columns are inserted in the spreadsheet. (You can make only the row or column cell reference absolute, if you wish.)
    • Another way to reference cells is numerically, in the format RxCy, where "R" indicates "row," "C" indicates "column," and "x" and "y" are the row and column numbers. Cell R5C4 in this format would be the same as Cell $D$5 in absolute column, row reference format. Putting either number after the "R" or the "C" makes that reference relative to the upper left corner of the spreadsheet page.
    • If you use only an equal sign and a single cell reference in your formula, you copy the value from the other cell into your new cell. Entering the formula "=A2" in Cell B3 will copy the value entered into Cell A2 into Cell B3. To copy the value from a cell in one spreadsheet page to a cell on a different page, include the page name, followed by an exclamation point (!). Entering "=Sheet1!B6" in Cell F7 on Sheet2 of the spreadsheet displays the value of Cell B6 on Sheet1 in Cell F7 on Sheet2.
  3. Use arithmetic operators for basic calculations. Microsoft Excel can perform all of the basic arithmetic operations �- addition, subtraction, multiplication, and division -� as well as exponentiation. Some operations use different symbols than are used when writing equations by hand. A list of operators is given below, in the order in which Excel processes arithmetic operations:
    • Negation: A minus sign (-). This operation returns the additive inverse of the number represented by the numeric constant or cell reference following the minus sign. (The additive inverse is the value added to a number to produce a value of zero; it's the same as multiplying the number by -1.)
    • Percentage: The percent sign (%). This operation returns the decimal equivalent of the percentage of the numeric constant in front of the number.
    • Exponentiation: A caret (^). This operation raises the number represented by the cell reference or constant in front of the caret to the power of the number after the caret.
    • Multiplication: An asterisk (*). An asterisk is used for multiplication to avoid confusion with the letter "x."
    • Division: A forward slash (/). Multiplication and division have equal precedence and are performed from left to right.
    • Addition: A plus sign (+).
    • Subtraction: A minus sign (-). Addition and subtraction have equal precedence and are performed from left to right.
  4. Use comparison operators to compare the values in cells. You'll use comparison operators most often in formulas with the IF function. You place a cell reference, numeric constant, or function that returns a numeric value on either side of the comparison operator. The comparison operators are listed below:
    • Equals: An equal sign (=).
    • Is not equal to (<>).
    • Less than (<).
    • Less than or equal to (<=).
    • Greater than (>).
    • Greater than or equal to (>=).
  5. Use an ampersand (&) to join text strings together. The joining of text strings into a single string is called concatenation, and the ampersand is known as a text operator when used to join strings together in Excel formulas. You can use it with text strings or cell references or both; entering "=A1&B2" in Cell C3 will yield "BATMAN" when "BAT" is entered in Cell A1 and "MAN" is entered in Cell B2.
  6. Use reference operators when working with ranges of cells. You'll use ranges of cells most often with Excel functions such as SUM, which finds the sum of a range of cells. Excel uses 3 reference operators:
    • Range operator: a colon (:). The range operator refers to all cells in a range beginning with the referenced cell in front of the colon and ending with the referenced cell after the colon. All the cells are usually in the same row or column; "=SUM(B6:B12)" displays the result of adding the column of cells from B6 through B12, while "=AVERAGE(B6:F6)" displays the average of the numbers in the row of cells from B6 through F6.
    • Union operator: a comma (,). The union operator includes both the cells or ranges of cells named before the comma and those after it; "=SUM(B6:B12, C6:C12)" adds together the cells from B6 through B12 and C6 through C12.
    • Intersection operator: a space ( ). The intersection operator identifies cells common to 2 or more ranges; listing the cell ranges "=B5:D5 C4:C6" yields the value in the cell C5, which is common to both ranges.
  7. Use parentheses to identify the arguments of functions and to override the order of operations. Parentheses serve 2 functions in Excel, to identify the arguments of functions and to specify a different order of operations than the normal order.
    • Functions are pre-defined formulas. Some, such as SIN, COS, or TAN, take a single argument, while other functions, such as IF, SUM, or AVERAGE, may take multiple arguments. Multiple arguments within a function are separated by commas, as in "=IF (A4 >=0, "POSITIVE," "NEGATIVE")" for the IF function. Functions may be nested within other functions, up to 64 levels deep.
    • In mathematical operation formulas, operations within parentheses are performed before those outside it; in "=A4+B4*C4," B4 is multiplied by C4 before A4 is added to the result, but in "=(A4+B4)*C4," A4 and B4 are added together first, then the result is multiplied by C4. Parentheses in operations may be nested inside each other; the operation in the innermost set of parentheses will be performed first.
    • Whether nesting parentheses in mathematical operations or in nested functions, always be sure to have as many close parentheses in your formula as you do open parentheses, or you'll receive an error message.

Entering Formulas

  1. Select the cell you want to enter the formula in.
  2. Type an equal sign the cell or in the formula bar. The formula bar is located above the rows and columns of cells and beneath the menu bar or ribbon.
  3. Type an open parenthesis if necessary. Depending on the structure of your formula, you may need to type several open parentheses.
  4. Create a cell reference. You can do this in 1 of several ways:
    Type the cell reference manually.
    Select a cell or range of cells in the current page of the spreadsheet.
    Select a cell or range of cells in another page of the spreadsheet.
    Select a cell or range of cells on a page of a different spreadsheet.
  5. Enter a mathematical, comparison, text, or reference operator if desired. For most formulas, you'll use a mathematical operator or 1 of the reference operators.
  6. Repeat the previous 3 steps as necessary to build your formula.
  7. Type a close parenthesis for each open parenthesis in your formula.
  8. Press "Enter" when your formula is the way you want it to be.

Tips

  • When you first start working with complex formulas, it may be helpful to write the formula out on paper before entering it into Excel. If the formula looks too complex to enter into a single cell, you can break it down into several parts and enter the parts into several cells, and use a simpler formula in another cell to combine the results of the individual formula parts together.
  • Microsoft Excel offers assistance in typing formulas with Formula AutoComplete, a dynamic list of functions, arguments, or other possibilities that appears after you type the equal sign and the first few characters of your formula. Press your "Tab" key or double-click an item in the dynamic list to insert it in your formula; if the item is a function, you will then be prompted to enter its arguments. You can turn this feature on or off by selecting "Formulas" on the "Excel Options" dialog and checking or unchecking the "Formula AutoComplete" box. (You access this dialog by selecting "Options" from the "Tools" menu in Excel 2003, from the "Excel Options" button on the "File" button menu in Excel 2007, and by selecting "Options" on the "File" tab menu in Excel 2010.)
  • When renaming the sheets in a multi-page spreadsheet, make it a practice not to use any spaces in the new sheet name. Excel won't recognize naked spaces in sheet names in formula references. (You can also get around this problem by substituting an underscore for the space in the sheet name when using it in a formula.)

Warnings

  • Don't include formatting such as commas or dollar signs in numbers when entering them in formulas because Excel recognizes commas as argument separators and union operators and dollar signs as absolute reference indicators.

Related Articles

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Type_Formulas_in_Microsoft_Excel&oldid=48601"