When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for...
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing:
=AVERAGE(first:last)
where "first" is a reference to the first cell...
The ROWS function is fully automatic. When you provide a range to ROWS, it will return a count of all rows in the range. In the example, the formula in F5 returns 6, because there are 6 rows in the range B5:C10:
=...
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14).
To get the last row used, we use...
This formula replies on a helper column that already contains a sequential list of numbers to represent an established sort order. The numbers in the helper column are independent from the operation of this formula....
The core of this formula is based on another formula that calculates the "current row" in a range:
=ROW()-ROW(rng.firstcell)+1
In brief, we get the current row in the workbook, then subtract the first row number of...
Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.
In the example shown, the date in the named range "...
The core of this formula is the ADDRESS function, which is used to return a cell address based on a given row and column. Unfortunately, the formula gets somewhat complicated because we need to use ADDRESS twice: once...
Note: this formula uses the named range "data" (B5:E104) for readability and convenience. If you don't want to use a named range, substitute $B$5:$E$104 instead.
To pull a random value out of a list or table, we'll...
Note: this formula uses two named ranges: "names" refers to C4:C11, and "groups" refers to B4:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...
This formula uses structured referencing, a syntax that allows table parts to be called out by name. When a table is called with the name only, Excel returns a reference to the data region of the table only. In this...
There is no built-in function for counting the total numbers of cells in a range, so you need to use the ROWS and COLUMNS functions together. In the example, ROWS returns the total number of rows in B5:C10 (6), and...
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains...