FORMULA TAB FUNCTION LIBRARY FORMULA AUDITING FUNCTION LIBRARY

FORMULA TAB FUNCTION LIBRARY FORMULA AUDITING

FUNCTION LIBRARY Excel has a library of several hundred functions on Formulas Tab that will let you perform a number of mathematical and statistical calculations. For example you can use the sum function to add numbers, average function to compute averages on a list of numbers, the PMT function to figure out payment on a loan, so on and so forth.

Financial Date & Time Math & Trigonometrically Statistical Lookup & Reference Function Library Database Text Logical Information Engineering Cube Compatibility

Types Of Functions Excel’s Functions categories: - are grouped into 10 • FINANCIAL - calculates interest rates, loan payments , depreciation , amounts etc. • DATE & TIME – returns the current hour , day of week or year, time or date. • MATHS & TRIGNOMETRICAL – calculates absolute values , cosines , logarithms etc. • STATISTICAL – calculates total, average, high and low numbers in a range; standard deviation etc. • LOOKUP & REFERENCE – searches for and return values from a range; creates hyperlinks to network or internet documents. . Continued on next slide

• DATABASE – calculates average , maximum , minimum etc. In an excel database table. • TEXT – converts text to upper or lower case , trims characters from the right or left end of a text string , concatenates text strings. • LOGICAL – evaluates an expression, and returns a value of ‘True’ or ‘False’ ; used to trigger other actions or formatting. • INFORMATION – returns information from excel or windows about the current status of a cell , object , or the environment. • ENGINEERING – included with Ms-office, but must be installed separately from the analysis tool pack. • CUBE – returns a member of tuple from an OLAP cube , calculates the number of items in a set etc. • COMPATIBILITY – contains the original statistical functions which existed in earlier versions of Excel(renamed).

Parts of a Function • • • Each function has a specific order—called syntax—which must be followed in order for the function to work correctly. The basic syntax to create a formula with a function is to insert an equals sign (=), function name (SUM, for example, is the function name for addition), and argument. Arguments contain the information you want the formula to calculate, such as a range of cell references.

Working with arguments Arguments must be enclosed in parentheses. Individual values or cell references inside the parentheses are separated by either colons or commas. • Colons create a reference to a range of cells. For example, =AVERAGE(E 19: E 23) would calculate the average of the cell range E 19 through E 23. • Commas separate individual values, cell references, and cell ranges in parentheses. If there is more than one argument, you must separate each argument by a comma. For example, =COUNT(C 6: C 14, C 19: C 23, C 28) will count all the cells in the three arguments that are included in parentheses.

Creating a basic function in Excel • Select the cell where the answer will appear (F 15, for example). • Type the equals sign (=), then enter the function name (SUM, for example). • Enter the cells for the argument inside the parentheses. • Press Enter, and the result will appear.

FORMULA AUDITING The process of examining a worksheet for errors in formulas is referred to as Formula auditing.

Trace Precedents You have to pay $96. 00. To show arrows that indicate which cells are used to calculate this value, execute the following steps. • Select cell C 13. • On the Formulas tab, in the Formula Auditing group, click Trace Precedents. • Result: As expected, Total cost and Group size are used to calculate the Cost person.

Remove Arrows To remove the arrows, execute the following steps. • On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

Trace Dependents • • Select cell C 12. On the Formulas tab, in the Formula Auditing group, click Trace Dependents. • Result: As expected, the Cost person depends on the Group size.

Show Formulas By default, Excel shows the results of formulas. To show the formulas instead of their results, execute the following steps. • On the Formulas tab, in the Formula Auditing group, click Show Formulas. • Result: However, instead of clicking show formulas, you can also press CTRL + (`). you can find this key above the tab key.

Error Checking To check for common errors that occur in formulas, execute the following steps. • Enter the value 0 into cell C 12. • On the Formulastab, in the Formula Auditing group, click Error Checking. • Result. Excel finds an error in cell C 13. The formula tries to divide a number by 0.

Evaluate Formula To debug a formula by evaluating each part of the formula individually, execute the following steps. • Select cell C 13. • On the Formulas tab, in the Formula Auditing group, click Evaluate Formula. • Click Evaluate four times. • Excel shows the formula result.

Thank You