Microsoft Office Tutorials and References

In Depth Information

5.
Activate the Refers To field and press Ctrl+V to paste the megaformula text.

6.
Click OK to close the New Name dialog box.

After performing these steps and creating the named formula, you can enter the following formula, and it will

return the result using the cell directly to the left:

=NoMiddleName

If you enter this formula in cell K8, it displays the name in cell J8, with no middle name.

See Chapter 3 for more information about creating and using named formulas.

This megaformula uses the IFERROR function, so it will not work with versions prior to Excel 2007. A com-

parable formula that's compatible with previous versions is

=LEFT(TRIM(A1),FIND(“ “,TRIM(A1),1)–1)&” “&RIGHT

(TRIM(A1),LEN(TRIM(A1))–IF(ISERROR(FIND(“ “,TRIM(A1),

FIND(“ “,TRIM(A1),1)+1)),FIND(“ “,TRIM(A1),1),FIND(“ “,TRIM

(A1),FIND(“ “,TRIM(A1),1)+1)))

Comparing speed and efficiency

Because a megaformula is so complex, you may think that using one slows down recalculation. Actually, that's

not the case. As a test, I created three workbooks (each with 175,000 names): one that used six intermediate for-

mulas, one that used a megaformula, and one that used a named megaformula. I compared the results in terms

of calculation time and file size; see Table 21-3.

Table 21-3: Comparing Intermediate Formulas and Megaformulas

Method

Recalculation Time (Seconds)

File Size

Intermediate formulas
3.1

13.5MB

Megaformula

2.3

3.07MB

Named megaformula
2.3

2.67MB

Of course, the actual results will vary depending on your system's processor speed.

As you can see, using a megaformula (or a named megaformula) in this case resulted in slightly faster recalcula-

tions as well as a much smaller workbook.