Purpose
Return value
Syntax
=ADDRESS(row_num,col_num,[abs_num],[a1],[sheet])
- row_num - The row number to use in the cell address.
- col_num - The column number to use in the cell address.
- abs_num - [optional] The address type (i.e. absolute, relative). Defaults to absolute.
- a1 - [optional] The reference style, A1 vs R1C1. Defaults to A1 style.
- sheet - [optional] The name of the worksheet to use. Defaults to current sheet.
Using the ADDRESS function
The ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return a relative, mixed, or absolute reference, and can be used to construct a cell reference inside a formula. Note that ADDRESS returns a reference as a text value . If you want to use this text inside a formula reference, you will need to coerce the text to a proper reference with the INDIRECT function .
Note: ADDRESS is a special purpose function and is not necessary in most formulas. For example, to retrieve a value at a specific row and column location, you can use INDEX and MATCH .
The ADDRESS function takes five arguments: row , column , abs_num , a1 , and sheet_text . Row and column are required, other arguments are optional. The abs_num argument controls whether the address returned is relative , mixed , or absolute , with a default value of 1 for absolute. The a1 argument is a Boolean that toggles between A1 and R1C1 style references with a default value of TRUE for A1 style references. Finally, the sheet_text argument is meant to hold a sheet name that will be prepended to the address.
ABS options
The table below shows the options available for the abs_num argument for returning a relative, mixed, or absolute address.
| abs_num | Result |
|---|---|
| 1 (or omitted) | Absolute ($A$1) |
| 2 | Absolute row, relative column (A$1) |
| 3 | Relative row, absolute column ($A1) |
| 4 | Relative (A1) |
Examples
Use ADDRESS to create an address from a given row and column number. For example:
=ADDRESS(1,1) // returns $A$1
=ADDRESS(1,1,4) // returns A1
=ADDRESS(100,26,4) // returns Z100
=ADDRESS(1,1,1,FALSE) // R1C1
=ADDRESS(1,1,4,TRUE,"Sheet1") // returns Sheet1!A1
Purpose
Return value
Syntax
=AREAS(reference)
- reference - Reference(s) to a cell or range of cells.
Using the AREAS function
The AREAS function returns the number of areas in a given reference as a number. In this context, areas mean separate contiguous ranges. AREAS takes just one argument , called reference . Reference can include more than one reference, but you must separate multiple references with a comma and wrap them in an extra set of parentheses. Otherwise, Excel will think the commas indicate multiple arguments and generate an error about entering too many arguments.
Examples
The formulas below show how the AREAS function can be configured. Notice the first example does not need an extra set of parentheses, since there is just one reference). However, the examples following do need the extra set of parentheses.
=AREAS(A1:C1) // returns 1
=AREAS((A1:C1,A2:C2)) // returns 2
=AREAS((F17:F19,J16:J18,I8)) // returns 3
