1 / 23

Excel chapter 2

Excel chapter 2. Nitin Chowdary Are Nitin.Are@mail.wvu.edu. Upcoming due dates . MyITLab Lesson A : 27 th Monday , before 11:59PM Homework 1 : 31 st Friday . Cell References Function Basics Function Categories and Description Different Functions. Cell References.

bobby
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. Upcoming due dates • MyITLab Lesson A : 27th Monday, before 11:59PM • Homework 1 : 31st Friday

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

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

  5. 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. ( eg. same row and two columns to the left, Two rows up and three columns to the right) • Cell reference changes when we copy the formula. • Lets see a simple example

  6. 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)

  7. 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 : Row absolute mixed ref(I call it as Column A is relative and row 4 is locked/absolute) • What about $A4 ?

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

  9. Function basics

  10. 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.

  11. Function categories and descriptions

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

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

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

  15. 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)

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

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

  18. Math and statistical functions

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

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

  21. 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.

  22. 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.

  23. 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