Using Functions in Excel

Using Functions in Excel

1. SUM, MIN, MAX, AVERAGE, COUNT, COUNTA

   • SUM: advantage of using a function: Saves time writing Simplifies complex calculations Example: instead of =C5+C6+C7+C8 use =SUM(C5:C8)
     • MIN: MIN(SUM(number1,[number2],…) Returns the smallest number of a range of values
       • MAX(SUM(number1,[number2],…) Returns the largest number of a range of values
         • The COUNT function ignores blank cells and text the resulting value is 0
           • What value will result in cell A5 if it contains the formula =AVERAGE(A1:A4)?

2. ROUND

   • Round (number, num_digits) = Round (24.44,1) results in the value 24.4

3. COUNTIF, SUMIF, AVERAGEI

   • The COUNTIF function counts the number of values that meet a specified criteria: =COUNTIF(B5:B8, “H”)
     • SUMIF(range, criteria, sum-range) Range – Continuous range used to compare the criteria Criteria – Comparison Criteria Sum-Range - If criteria is met, the computer will sum the corresponding entry in this range
       • AVERAGEIF(range, criteria, averagif-range) – works identically to the SUMIF function except it averages the specified range.

4. CONSOLIDATE

   • Sumarize data from separate range, consolidating the results, in a single output range.

5. IF.ERROR

   • Syntax IFERROR(value, value_if_error) The IFERROR function syntax has the following arguments:

6. IFCONDITIONAL

   • esting whether conditions are true or false and making logical comparisons between expressions are common to many tasks. You can use the AND, OR, NOT, and IF functions to create conditional formulas. For example, the IF function uses the following arguments.