ADDRESS(3 4) returns $D$3 rather than $D$4 and Betty’s quiz scores are returned instead of Bob’s. MATCH returns an offset into the provided range, so MATCH("Bob" A2:A16 0) returns 3 rather than 4. Consider, for example, what happens if the range is changed to A2:A16. Returns 4 because Bob is the fourth entry in column A.Ĭonverts $D$4 into a reference to the cell D4.Īlthough Listing 10 works as intended, it breaks easily and unexpectedly. Table 12 isolates each function in Listing 10, providing an easy to understand explanation of how the example works. In its entirety, Listing 10 is complex and difficult to understand. Using values from Table 1, Listing 10 uses OFFSET to obtain the quiz scores for the student named Bob. If the height is present, then a range reference is returned rather than a cell reference. If the height or width are missing, they default to 1. If both the width and height are missing, a cell reference is returned. If the width or height is included, the OFFSET function returns a range. OFFSET(reference rows columns height width) The OFFSET function has the following syntax: The second and third arguments specify the number of rows and columns to move from the reference point in other words, where the new range starts. The first argument, specifies the reference point.
Use OFFSET to return a cell or range offset a specified number of rows and columns from a given reference point. OFFSET returns a cell or range offset from another Returns the contents of cell $A$2, which is Bob. Return the sum of the range B1:B5, which is 194. If Cell G1 contains the text A2, then this returns Bob. Table 11 contains examples accessing data as shown in table 9. Use INDIRECT to convert a string representation of a cell or range address to a reference to the cell or range. INDIRECT converts a string to a cell or range Use a relative row reference and an absolute column reference. Use an absolute row reference and a relative column reference. This is the default value if the argument is missing or an invalid value is used. Values supported by the abs argument to ADDRESS. For example, the ROW, COLUMN, ROWS, and COLUMNS statements are not discussed a curious person would investigate these functions. Calc supports numerous powerful functions that are not discussed here.
0 kommentar(er)
