Function Formulas
Home Up Excel Basics Changing Worksheet Appearance Organizing the Worksheet Worksheet Formulas Function Formulas Making the Worksheet Useful Working with Multiple Worksheets Worksheet Charts Other Links

 

Student Objectives: Identify the parts of a function formula.; Use function formulas to solve mathematical problems.; Use function formulas to solve statistical & financial problems.; Use function formulas to insert times and dates.

What are Function formulas?
1) Special formulas that do not use operators to calculate a result.  They perform complex calculations in specialized areas of mathematics, statistics, logic, trigonometry, accounting, and finance.
2) They are also used to convert worksheet values to dates and times.

What are the 3 components of a Function Formula?
1) Equal Sign: all formulas in Excel begin with an equal sign.
2) Function name: Identifies the operation to be performed (Ex. SUM, AVG, MIN, COUNT)
3) Argument: a value, cell reference, range, or text that acts as an operand in a function formula. It is enclosed in parenthesis (Ex. D5, 55;)

Function formulas may be entered in the worksheet in 3 ways....
1) Entering or typing the function formula manually into the cell.
2) By choosing Insert, Paste Function from the menu-bar.
3) By clicking the Paste Function button on the toolbar.

What are Mathematical and trigonometric function formulas?
1) Mathematical formulas: manipulate quantitative data in the worksheet.   They are mathematical operations such as addition, subtraction, multiplication, and division.  They also include the square root, rounding, and logarithms. (SQRT, ROUND, SUM)
2) These do not always require function formulas (ex. adding, subtraction, multiplication, division, etc.)

What are statistical formulas?
1) They are used to describe large quantities of data.
2) Examples of them are Average, Count, Minimum (MIN), Maximum (MAX); Standard deviation (STDE); Variance (VAR)

What are the various and most common statistical function formulas and what is the command?
                        Function Formulas
    a) Average          =AVERAGE(A3:A5)
    b) COUNT             =COUNT(A3:A5) This formula is used to count the number of in a series, for example, the number of persons taking an exam.
    c) MAX                =MAX(A3:A5) This finds the highest number or maximum in a series.
    d) MIN                 =MIN(A3:A5) This finds the lowest number or minimum in a series.
    e) ROUND            =ROUND(14.23433) This rounds any number that is selected.
    f) SQRT                =SQRT(C4) This finds the square root of a cell.
2) How do I do it?  The command is Insert, Function or the Paste Function button.

What are financial formulas?
1) They are used to analyze loans and investments.
2) The primary financial functions are future value (FV); present value (PV); and Payment (PMT).

What are logical functions?
1) They are used to display text or values if certain conditions exist.  It uses the IF function formula.
2) A teacher may use an IF function to determine whether a student has passed or failed a course. [Ex. IF(C4>69,"PASS", "FAIL").