Continuation of Functions
~ Start functions with an equal sign (=) followed by the FUNCTION NAME (in either upper- or lowercase), followed by an open parenthesis, the range to be affected and a close parenthesis.
~ A function may be used by itself, or it may be combined with other functions. Excel provides many functions, some of which are used for statistical and financial analysis, while others are used in database operations. A function may be entered using the keyboard or by locating the Functions button on the Home Ribbon Tab.
Average Function
~ The Average Function averages values in a range. For example, to average the values in A4, A5 and A6, the function would appear as =AVERAGE(A4:A6). The data the functions require you to supply are called the ARGUMENTS. For example, in =AVERAGE(A4:A6), the data range A4:A6 is the argument.
- Note: The beginning cell and ending cell in a range are separated by a colon. The range reads: "A4 through A6".
- The following illustrates the various parts of a function, and all functions follow the same format:
= Average (A4:A6)
Equal Sign Function Name Argument
Count, Min and Max Functions
- The Count function counts all the non-blank cells in a range. Cells containing values, formulas or text are counted.
- Min indicates the lowest value in a range.
- Max indicates the highest value in a range.
If Statement
- An If statement is a logical function which sets up a conditional statement to test data. The truth or falsity of the condition will determine the results of a the statement.
- The format for an If Statement is:
=IF(CONDITION,X,Y)
- Note that the IF Statement is preceded by an equal sign (=). If the condition is true, the function results in X; if the condition is false, the function results in Y. **
- In this example, the teacher uses an IF Statement to determine the final grade depending on the final average. The passing grade is a C, or 71. Therefore, an IF Statement can be used to test whether the final average is greater than 70.5. If the condition is true (that the average is greater than 70.5), the student passes and the letter P is entered in the function location. If the condition is false, the letter F is entered in the function location. A breakdown of one of the IF statements used in this example is shown below:
Condition Then Else
=IF(E8>70.5, "P","F")
condition If true, If false,
(Is grade greater P is F is
than 70.5?) entered entered
in cell. in cell.
- If a label is to be used as an outcome of an If statement, it must be enclosed in quotes. Notice the position of the quotes in relationship to the commas in the example above.
- If statements use condition operators of:
= Equals > Greater than <Less than
<> Not equal to >= Greater than or equal to <= Less than or equal to
** X and Y can be the 3 things: They can be 1) numbers or values
2) labels or words ("must be in quotes")
3) formulas or functions