Microsoft Office Tutorials and References
In Depth Information
USE TIMER TO MICRO-TIME EVENTS
To make Juan Pablo Gonzalez’s function easier, Al_B_Cnu wrote Fuzzy-
VLOOKUP and FuzzyHLookup . Like VLOOKUP, the FuzzyVLOOKUP function
can return a speciﬁ c column from a table. It can also return the best match,
second-best match, and so on. In Figure 129, FuzzyVLOOKUP returns the
three best matches for each forecasted customer. Someone is going to have
to go through the choices to ﬁ gure out which entries are correct matches.
Part
3
Figure 129. FuzzyVLOOKUP compares every item in the list to
ﬁ nd the best, second-best, and third-best matches.
Summary: Custom functions in VBA help solve the fuzzy match problem.
USE TIMER TO MICRO-TIME EVENTS
Challenge: Two Excel gurus walk into a bar. One of them says it is faster
to use =MAX(0,MIN(A2,B2)) , and the other thinks it is better to use
=MEDIAN(0,A2,B2) . Which one is correct?
Solution: You can ﬁ nd the answer by ﬁ ring up a VBA macro to calculate each
formula 50,000 times. Before the macro starts, you save the value of Timer
to a variable. When the 50,000 calculations end, you can compare the original
and ﬁ nal values of Timer .
On a Windows PC, Timer shows the number of seconds and fractional seconds
elapsed since midnight. On a Mac, the function returns only whole seconds but
no fractions. You need to make the process repeat enough times to actually
show a difference in the number of seconds. Also, you need to make sure that
the process does not extend past midnight!

Search JabSto ::

Custom Search