Microsoft Office Tutorials and References

In Depth Information

The formula uses two arrays, specified by brackets. See Chapter 14 for more informa-

tion about using arrays in formulas.

Figure 5-6 shows the formula in use.

Figure 5-6:
Using a formula to express a number as an ordinal.

Determining a column letter for a column number

This next formula returns a worksheet column letter (ranging from A to XFD) for the value contained in cell

A1. For example, if A1 contains
29,
the formula returns
AC
(the 29th column letter in a worksheet)
.

=LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)

Note that the formula doesn't check for a valid column number. In other words, if A1 contains a value less than

1 or greater than 16,384, the formula then returns an error. The following modification uses the IFERROR func-

tion to display text (
Invalid Column
) instead of an error value:

=IFERROR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1),”Invalid

Column”)

The IFERROR function was introduced in Excel 2007. For compatibility with versions prior to Excel 2007, use

this formula: