1 / 35

Day 5: Excel Chapter 2

Day 5: Excel Chapter 2. Tazin Afrin Tazin.Afrin@mail.wvu.edu September 03, 2013. Fomula Basics. Semi selection / pointing Using cell reference Basic functions Logical, lookup and financial functions. Semi-selection.

abner
Télécharger la présentation

Day 5: 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. Day 5:Excel Chapter 2 Tazin AfrinTazin.Afrin@mail.wvu.edu September 03, 2013

  2. Fomula Basics • Semi selection / pointing • Using cell reference • Basic functions • Logical, lookup and financial functions

  3. Semi-selection • Semi-selection is the process of using the mouse pointer to select cells while building a formula. • Called pointing because you use the mouse pointer to select cells as you build the formula. • Used to select cell/ranges.

  4. Cell reference • When copying formula to other cell, ask yourself : • Do the cell references need to adjust for the copied formulas ? • should the cell references always refer to the same cell location, regardless where the copied formula is located?

  5. Cell reference • 3 types : • Relative reference • Absolute reference • Mixed reference

  6. Relative Cell reference • Indicates a cell’s relative location from the cell containing the formula • such as two rows up and one column to the left • The cell reference changes when the formula is copied • maintain the same relative distance from the copied formula cell • Example, =A8-B8

  7. Relative Cell reference Relative cell reference Selected cell

  8. Relative Cell reference Relative cell reference Selected cell

  9. Relative Cell reference • Why this happen ? • Because you copy the formula down the column to cell C12 • the column letters in the formula stay the same, but the row numbers change, down one row number at a time. • Using relative cell addresses ensures this calculation for each borrower • amount financed = house cost - down payment

  10. Absolute Cell reference • Indicates a cell’s specific location • provides a permanent reference to a specific cell • the cell reference does not change when you copy the formula • Regardless of where you copy the formula • Appears with a dollar sign before both the column letter and row number, such as $B$5.

  11. Absolute Cell reference Absolute cell reference Formulas referring to this cell should contain an absolute reference Selected cell

  12. Absolute Cell reference Formulas referring to this cell should contain an absolute reference

  13. Absolute Cell reference • If Down payment rate in cell B5 changes from 20% to 25% then down payment for all the borrowers changes. • For C8 = A8*$B$5 • A8 is relative reference, changes as you copy the formula to C9 • *$B$5 is absolute reference, does not change to B6

  14. Mixed Cell reference • Contains both an absolute and a relative cell reference in a formula • combines an absolute cell reference with a relative cell reference • The absolute part does not change but the relative part does when you copy the formula. • either the column letter or the row number

  15. Mixed Cell reference • Example – • $B5 or B$5 is a mixed cell reference • $B5, the column B is absolute, and the row number is relative; when you copy the formula, the column letter, B, does not change, but the row number will change. • B$5, the column letter, B, changes, but the row number, 5, does not change.

  16. Mixed Cell reference Mixed cell reference Selected cell

  17. Mixed Cell reference • Because you are copying down the same column, only the row reference 5 must be absolute; the column letter stays the same

  18. Shortcut keys • The F4 key toggles through relative, absolute, and mixed references. Click a cell reference within a formula on the Formula Bar, and then press F4 to change it. • For example, click in B5 in the formula =A8*B5. Press F4, and the relative cell reference (B5) changes to an absolute cell reference ($B$5). • Press F4 again, and $B$5 becomes a mixed reference (B$5); • press F4 again, and it becomes another mixed reference ($B5). • Press F4 a fourth time, and the cell reference returns to the original relative reference (B5).

  19. Avoiding Circular Cell reference • A circular reference occurs when a formula directly or indirectly refers to itself. • =A8-C8 in cell C8

  20. Function basics • A 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. • An argument is an input, such as a cell reference or value, needed to complete a function

  21. Inserting function • Formula AutoComplete displays a list of functions and defined names as you enter a function. • Select a cell • Type an equal sign • Begin typing the function name • If you type =SU, Formula AutoComplete displays a list of functions and names that start with SU

  22. Inserting function

  23. Function screen tip • Function ScreenTip, a small pop-up description that displays the function’s arguments

  24. Function dialog box Input Values Function result Definition

  25. Basic Statistical Function • =AVERAGE(number 1,[number2], . . .) • =MEDIAN(number 1,[number 2], . . .) • =MIN(number 1,[number 2], . . .) • =MAX(number 1,[number 2], . . .) • =COUNT(number 1,[number 2], . . .) • =COUNTBLANK(number 1,[number 2], . .) • =COUNTA(number 1,[number 2], . . .)

  26. Date Function • =TODAY() • =NOW()

  27. Logical Function • The IF function evaluatesa condition and returns one value if the condition is true and a different value if the condition is false. • =IF(logical_test,value_if_true,value_if_false) • The logical test is an expression that evaluates to true or false. • result is either true or false

  28. Logical operators

  29. Lookup Function • A lookup table is a range that contains data for the basis of the lookup and data to be retrieved • The breakpoint is the lowest value for a specific category or series in a lookup table

  30. vLookup Function • The VLOOKUP function looks up a value in a vertical lookup table and returns a related result from the lookup table. • =VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])

  31. vLookup Function =VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup]) • The lookup value is a reference to a cell containing a value to look up. • The table array is a range containing a lookup table. • The column index number isthe argument in a VLOOKUP function that identifies which lookup table column from which to return a value.

  32. vLookup Function

  33. hLookup Function • The HLOOKUP function looks up a value in a horizontal lookup table where the first row contains the values to compare with the lookup value. • =HLOOKUP(lookup_value,table_array,row_index_number,[range_lookup])

  34. Next Class • PMT function • Range names • Imports • Web queries • Xml

  35. Thank You Log Off

More Related