1 / 22

Excel chapter 2

Excel chapter 2. Nitin Chowdary Are Nitin.are@mail.wvu.edu. Cell References Function Basics Function Categories and Description Different Functions. Cell References. What do you mean by cell reference! Once recall AUTOFILL function Three types : Relative Cell Reference

shirin
Download Presentation

Excel chapter 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel chapter 2 Nitin Chowdary Are Nitin.are@mail.wvu.edu

  2. Cell References • Function Basics • Function Categories and Description • Different Functions

  3. Cell References • What do you mean by cell reference! • Once recall AUTOFILL function Three types: • Relative Cell Reference • Absolute Cell Reference • Mixed Cell Reference

  4. Relative Cell reference • Indicates a cell’s relative location from the cell containing the formula. • What do you mean by relative location ! • Location of cell with respect to cell containing formula. • Cell reference changes when we copy the formula. • Lets see a simple example

  5. Absolute cell reference • Indicates a cell’s specific location. • Cell reference does not change when we copy the formula. • Simply called as locking the reference even we copy the formula. We can do this by inserting $ symbol in cell address. • Press F4 to make it absolute. • E.g. $A$4 (I call it as column A locked/absolute and row 4 locked/absolute)

  6. Mixed cell reference • Contains both an absolute and a relative cell reference in a formula. • Either column or row is absolute/locked and the other is relative. • E.g. A$4 (I call it as Column A is relative and row 4 is locked/absolute)

  7. Avoid circular references • Occurs when formula directly or indirectly refers to itself. • Gives you warning.

  8. Function basics

  9. Function basics • Function is a predefined formula that performs a calculation. • Syntax is a set of rules that govern the structure and components for properly entering a function. • Argument is an input, such as a cell reference or value, needed to complete a function.

  10. Function categories and descriptions

  11. Inserting a function • Formula AutoComplete displays a list of functions and defined names as you enter a function. • Function ScreenTip (gives you syntax) • Insert Function Dialog Box

  12. Sum function • Calculates the total of values contained in two or more cells. • Syntax: SUM(number1,[number2],…..)

  13. Average function • Calculates the arithmetic mean, or average, of values in a range. • Syntax:AVERAGE(number1,[number2],..)

  14. MEDIAN Function • Identifies the midpoint in a set of values. • SYNTAX: MEDIAN(number1,[number2],…) • Know the difference when you give ODD number of arguments (gives the middle one) and EVEN number of arguments (gives the average of middle two numbers)

  15. MIN and max functions • Syntax: • MIN(number1,[number2],…..) • MAX(number1,[number2],….)

  16. How to know about the function ! • Use HELP ON THIS FUNCTION/CLICK on the function.

  17. Math and statistical functions

  18. Date/time functions • Note : Serial number is the number starting from Jan 1,1990.

  19. Logical functions • IF function, syntax: • IF(logical_test/condition, value_if_true, value_if_false) • Logical operators

  20. Lookup Functions • Lookup table is a range that contains data for the basis of the lookup and data to be retrieved. • Breakpoint is the lowest value for a specific category or series in a lookup table. • VLOOKUP AND HLOOKUP • Syntax • =VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup]) • Mostly Used in two situations • Finding names/details • Finding category given a table of categories.

  21. Pmt function • Calculates payments for a loan with a fixed amount with a fixed periodic rate for a fixed time period. • Syntax : PMT(rate, nper, pv, [fv],[type]) • Rate- periodic interest rate • Nper- total number of payments periods • Pv- present value of the loan / principal. • Fv- futre value of loan /  cash balance you want to attain after the last payment is made. • Type- when payments are due.

  22. Range Names • Create a range name • Click in the Name Box, type the range name, and then press Enter. • Click the Formulas tab, click Define Name in the Defined Names group to open the New Name dialog box, type the range name in the Name box, and then click OK. • Edit or Delete a Range Name • Click the formulas tab, click Name Manager in the Defined Names group to open the Name Manager dialog box, click New, type the range name in the Name box, click OK, and then click Close. • Using Range Names in Formulas.(E.g. VLOOKUP)

More Related