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:
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
FIND(“ “,TRIM(A1),1)+1)),FIND(“ “,TRIM(A1),1),FIND(“ “,TRIM
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
Recalculation Time (Seconds)
Intermediate formulas 3.1
Named megaformula 2.3
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.