Microsoft Office Tutorials and References
In Depth Information
Megaformula Examples
This megaformula uses the IFERROR function, so it will not work with versions prior to Excel
2007. A comparable 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 formulas, 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 20-3.
Table 20-3: Comparing Intermediate Formulas and Megaformulas
Method
Recalculation Time (Seconds)
File Size
Intermediate formulas
5.2
13.5MB
Megaformula
2.8
3.07MB
Named megaformula
2.8
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 faster
recalculations as well as a much smaller workbook.
The three test workbooks that I used are available on the companion CD-ROM. The
filenames are time test intermediate.xlsx , time test megaformula.xlsx , and
time test named megaformula.xlsx . To perform your own time tests, change the
name in cell A1 and start your stopwatch (your cell phone probably has one). Keep your
eye on the status bar, which indicates when the calculation is finished.
Using a megaformula to return a string’s last space character position
As previously noted, the “remove middle name” example presented earlier contains a flaw: To
identify the last name, the formula searches for the second space character. A better solution is
to search for the last space character. Unfortunately, Excel doesn’t provide any simple way to
locate the position of the first occurrence of a character from the end of a string. The example in
this section solves that problem and describes a way to determine the position of the first
occurrence of a specific character going backward from the end of a text string.
 
Search JabSto ::




Custom Search