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:
Search JabSto ::




Custom Search