Formulas

What can formulas do?

    Formulas are one of the main components that give Excel its versatility. Using formulas, the user can create a functional, dynamic array of data, rather than a static table. Both text and numbers can be manipulated with formulas. Cell values beginning with an equal sign are interpreted by Excel as formulas.

Formula Syntax and Creation

    Formulas may be entered two ways: with the formula bar or by typing the formula as the cell's value. Clicking on the "fx" button on the formula bar (Figure 1) starts the Function dialog, where a function can be selected. Once a function has been selected, the Function dialog will prompt the user to enter the parameters of the function.

(Figure 1)

    In addition to using the function dialog, one can simply enter the formula as the cell's contents. Once the formula has been entered, the cell will display the result of the formula as the cell's value. The formula may be edited later, just as one would revise a cell's value if the cell contained regular data.

    A formula follows a simple pattern of <function name>(parameter1,parameter2,...), with all formulas having as their first character the equal sign. For example, "=sum(3,5)" would display 8, as shown in Figure 2. Notice the formula is the cell's contents, shown in the formula bar, and the cell displays the result of the function. Sum is a function that adds all of the parameters in the parentheses. Parameters of functions are separated by a comma. Cells and other functions can be parameters themselves. In fact, that would be a more common use for the "sum" function. If one wanted to add all of the cells in column D, rows 1-4, the formula "=sum(D1:D4)" would be used. That can also be combined with other values. The formula "=sum(D1:D4,3)" would add the contents of D1-D4 and then add 3 to that sum.

    Functions can be parameters, entered in the same manner as cells in the above mentioned example. For instance, "=dollar(sum(3,5))" would give a result of "$8.00"1. The user must make sure to include matching parentheses.

(Figure 2)

Output and Variable Types

    Since the result of a function is displayed as the cell's value, functions can be used to format and construct information. One example of such use is combining data and text. Figure 3 shows an example of this use. Column A contains numbers, which are summed in B1. This example creates a sentence that is more user friendly, constructing a sentence to include the sum. The formula used here is ="The sum of column a is " & sum(a:a). The ampersand, &, is used to connect text values. Using the plus sign would result in an error, because the plus sign adds numbers. Numbers and text can be combined with the use of either the ampersand or the function "concatenate." This example could have been done with the formula =concatenate("The sum of column a is ",sum(a:a)).

 

(Figure 3)

Commonly Used Functions

    Sum

        sum(number1, number2, ...). Adds the values within parentheses.

           Example:  =sum(3,4)  -> 7    or   =sum(A3:A5)  -> Total of the contents of A3-A5

   Count

        count(value1, value2, ...). Counts the number of cells which contain or refer to numbers

          Example: =count(A1,B2:C3)   -> the number of cells in A1 and B2-C3 which contain numbers

    If

          if(condition, value if condition is true, value if condition is not true).  Displays the second parameter if the

          condition is true and displays the third parameter if the condition is not true.

          Example:  =if(B2<4,"4 is bigger","4 is not bigger") If B2 is greater than 4, 4 is not bigger is displayed.

          If B2 is smaller than 4, 4 is bigger will be displayed.

   Iserr

         Iserr(value).  Equals true if value creates an error and equals false if value is a valid formula. This is useful if a

        different value is assigned if an error is created.

        Example:  =iserr(dollar("Text")) -> TRUE  ,     =iserr(dollar(3)) -> FALSE

Errors

    #Value!

        This error is caused if an parameter or argument of the wrong type is given. For example, using text when a

          number is expected.

    #Name?

        This error is caused if a name is used that has not been defined.

   #Ref!

      This error is caused if a value is referenced that does not exist, such as a sheet that has not been created.



1. Of course, this could have been done with formatting, but this example was chosen to illustrate the use of the dollar function. There may be instances in which it would be advantageous to use a function to set the format. For instance, if the user wanted to use the dollar format in only certain instances, the dollar function could be used with an "if" function.

Copyright 2008 David Coss davecoss.com