1 / 8

Cell Referencing

Cell Referencing. Absolute Reference: A reference that does not change when copied. It is specified with a dollar sign in front of both the row and column. ( $G$3 ) Relative Reference: A reference that adjusts during a copy operation and is specified without dollar signs. ( G3 )

stew
Download Presentation

Cell Referencing

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. Cell Referencing Absolute Reference: A reference that does not change when copied. It is specified with a dollar sign in front of both the row and column. ($G$3) Relative Reference: A reference that adjusts during a copy operation and is specified without dollar signs. (G3) Mixed Reference: a reference that adjusts either the row or column reference but not both. It is specified with a single dollar sign. ($G3 or G$3)

  2. Cell referencing is important during a move or copy operation. Copy: Duplicate the contents of a cell or range. Move: Transfer the contents of a cell or range. Range: Rectangular group of cells, which are specified by indicating the diagonally opposite corners

  3. Sample Cell Reference in a Formula A1 Relative address of the cell in row A and column 1; both the row header and column header may change when the formula is copied. $A$1Absolute address of the cell in row A and column 1; both the row header and column header remain constant when the formula is copied. A$1Absolute row, relative column addressing; only the column header may change if copied to horizontally. $A1Relative row, absolute column addressing; only the row header may change if copied vertically. R1C1 Relative addressing; refers to one column down and one column right when the formula is copied.

  4. IF FUNCTION =IF (condition, value_if_true, value_if_false) value returned for a false condition value returned for a true condition condition is either true or false

  5. Relational Operators = Equal to <> Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to

  6. =IF (A1=A2, 1000, 2000) =IF (A1<>A2, 1000, 2000) =IF (A1=A2, B1, B2) =IF (A1<B2, MAX(B1:B2), MIN(B1:B2)) =IF (A1<A2, B1+10, B1-10) =IF (A1=A2, C1, C2) =IF (SUM(A1:A2)>20, “Go”, “Hold”)

  7. VLOOKUP FUNCTION Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.

  8. VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) Lookup_value: value to be found in the first column of the array. Table_array:the table of information in which data is looked up. If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. *** Uppercase and lowercase texts are equivalent. Col_index_num: the column number in table_array from which the matching value must be returned. Range_lookup: a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

More Related