As you probably know, there’s a very useful Excel formula called
VLOOKUP() which allows you to reference lookup tables and return the appropriate value.
On an Excel user support forum, I had previously linked to some formulas that provided “fuzzy” vlookups. In the event a perfect vlookup match couldn’t be found, the custom formula
FUZZYVLOOKUP() returns the closest match available. The post has since 404’d, so I was asked to rehost them.
Click here to download a sample workbook.
If you open up developer mode, you’ll see a code module containing two custom functions:
FUZZYVLOOKUP(). The former compares two text strings and returns a numerical measure of how similar they are (1 is perfectly similar, 0 is completely dissimilar). The latter iterates the likeness formula across a lookup table and returns the appropriate column of the best match. Both of these functions are demo’d below:
Please note that
FUZZYVLOOKUP() uses four arguments:
- The lookup value.
- The lookup table.
- The column from the lookup table you’d like to return.
- The minimum acceptable likeness. (In this example, I used 0.2).
This post is part of the “Matrix Multiplication in Excel” series. It’s composed of a math introduction, a silly interlude and an interactive tutorial (you are here). By the end of the series, you’ll learn how to perform Markov Chain calculations, which are used in some damage calculations.
Now that we know some of the basics, I’m going to introduce a toy problem where one would use matrix multiplication, step through how to calculate it in Excel and then give an example used forensic economics.
Click here to download Matrix Multiplication.xlsx.
Scenario 1: Ping Pong Problem
Suppose two ping pong players (labelled left and right) are tied near the end of a game. In order to win, one of them has to have a two point lead. Suppose that, on any given volley, the left player has a 55% chance of winning.
The game can be thought of as having five states:
- (2,0) – The left player wins.
- (1,0) – The left player is winning by one.
- (0,0) – The game is tied.
- (0,1) – The right player is winning by one.
- (0,2) – The right player wins.
Graphically, the transitions between these states can be seen as follows.
The left player increases his score with the probability , in this case 55%. How do we calculate the probability that the left player wins? Continue reading →
I was asked to review another forensic accountant’s workpapers as a part of a consulting engagement. When I received the file, it totaled over 18 MB. By making a single change, I was able to lower its size to 53 KB. The next time you come across an oddly bloated Excel file, follow these steps.
- On each sheet, press Ctrl + End. This moves the cursor to the last cell Excel stores. In this case, one of sheets took me to column BO, row 1,048,576. Although only the first 39 rows contained meaningful data, the remaining rows stored formatting information, which Excel dutifully saved.
- Select all rows you don’t need (in this case, I pressed Ctrl + G and entered “40:1048576”) and delete the information (Ctrl + –). Make sure you select and delete the entire row / column as appropriate (Shift + Spacebar and Ctrl + Spacebar, respectively).
- Enjoy your faster load times and lower storage costs.