Excel Functions That Should Exist, and Making Your Own

Maybe other engineers have had different experiences, but I know that during my degree, Microsoft Excel became a very close and special friend to me. Sure, it’s not the most powerful software, but it can do any calculation that you could do on paper, and it will do it over and over and over again. This makes it great for iterative calculations.

God I love a good spreadsheet.

The problem with Excel is that, like most good pieces of software, it was written by a person. This means that it was written by exactly the kind of jerk who would deliberately and maliciously write the code in a way that slightly inconveniences me. Those guys are the worst.

Okay, maybe I’m exagerating. But there are certain functions in excel that aren’t quite right, or haven’t been followed through to their logical conclusion (although I’ll touch on the likely reasons for this in a moment). This post is about addressing these shortcomings, and writing functions that will work the way you want them to.

Writing User Defined Functions (UDF):

To write your UDF, hit   Alt + F11   to bring up the VBA (Visual Basic for Applications) window. Select a location to store the function. Your options are as follows:

  1. Worksheet
  2. Workbook
  3. A Module

locations for VBA code

Simply double click on the worksheet, workbook, or Module in which you want to insert the code. If you want to use a module, simple click INSERT – MODULE, as shown below. You can rename these modules whatever you want, and export them for use in other spreadsheets. I like modules, as they are easy ways of tidying up your code library within a spreadsheet

insert module

Note: Ensure that you are writing your code inside the right VBA Project. You’ll see in the panel on the left of the VBA window  that there is a VBA project for each spreadsheet open, as well as any Add In spreadsheets running in the background. So just select the worksheet, workbook or module within the appropriate project.

Now, the fun bit: Coding.

Simply start writing your function using the following:

Function Functionname(value1, value2, value3)

You can specify the variable type for the values given in brackets, e.g. value1 As Range, or you can leave it implicit.

Excel should completed the End Function statement for you, and you can start writing your code in between these statements:

functionResult=value1+value2+value3

Within your function, simply set the output value by using a line similar to:

Functionname = functionResult

Putting it all together, in this example, you are left with your User Defined Function, which should look something like this:

Function Functionname(value1, value2, value3)
functionResult = value1 + value2 + value3
Functionname = functionResult
End Function

To use this function just type in “=Functionname(1,2,5)” in a cell, to return the value “8”. You can refer to a cell for any (or all) of the inputs, and it will use the value inside that cell.

Once you’ve written your function, if it’s one that you’ll use again and again, you can copy and paste it into the VBA project for any new spreadsheet that you need to use it in. Alternatively, if you only need to use these spreadsheets on YOUR computer (or if you want the code stored somewhere handy for easy pasting into new spreadsheets without having to find the original), you can post the code into a module in a blank spreadsheet, and save that spreadsheet as an Add In (.XLAM format) (see image below), then activate the add-in. Once you’ve done this, any time you open up Excel your computer will open up the XLAM file and make any SUBs or FUNCTIONs in it available to you. (Note: if you send your spreadsheet to someone who doesn’t have your add-in, it won’t operate correctly as the code isn’t availble, so in that case it’s good to save the function in the WORKBOOK of that specific spreadsheet). I’ll do another post shortly about how to add it to the list of functions displayed when you pres the “fx” button, as well as how to show descriptions for the inputs of the function.

saving Excel Addin

As an example of where these custom Add Ins are useful, I have add-ins that I use at work for quick “back of the envelope” calculations (e.g. “how much power would the conveyor need to lift 20m?” or “what would the full load torque of that motor be?”), and since I generally have Excel open, I can simply use my LiftPower() or FLT() functions in excel to get a quick answer.

Example: Lookup

I’m sure many of you are familiar with the shortcomings of the excel Lookup functions (LOOKUP, VLOOKUP, HLOOKUP). Primarily these shortcomings are:

  • Formatting limitations (the lookup column must be sorted in ascending order)
  • Result limitations (minimal control over what to do if no exact match is found, or if multiple matches are found)

After looking at how these functions work, it’s obvious that the code was written so as to give a reasonably workable function, while minimising processing requirements. Since excel spreadsheets can get quite large, an older computer could get quite bogged down in running the multitude of comparisons required by the Lookup function, so it seems like they let it shortcut out of the code by exiting the loop and returning the result value as soon as the value in the next lookup cell is greater than the value being searched for. Presumably the decision was made that it was less likely that people would be bothered by having to sort their data in ascending order (for the lookup column) than they would be by the slowness caused by the spreadsheet running through excess code.

As for shortcomings in the outputs, this is a result of an assumed philosophy of keeping things simple. An increased number of options just increases the difficulty of using the function.

At the end of the day, anyone who is so into Excel that they aren’t entirely happy with how these functions operate, is probably into it enough to write their own User Defined Functions.

Thank the heavens for VBA. Can I get an “Amen!”?

In a fit of pique, I wrote my own lookup function, which I call Ulookup (Ultra Lookup). Depending on the option selected, the function will search the Lookup array and return one of the following types of result from the Results array:

  1. Get first exact value
  2. Get last exact value
  3. Get nearest value
  4. Get nearest value below
  5. Get nearest value over
  6. Return “” if not found
  7. Interpolate between two nearest results
  8. Sum of exact results

Here’s the code for my Ulookup (I’ll upload a link to the actual XLAM format add-in as an example, just as soon as I find somewhere to store it):

Function Ulookup(value, lookupArray As Range, resultsArray As Range, lookupType As Integer)

temp = “error”

Dim temp1, temp2, a, b, Ra, Rb As Long
Dim check As Boolean
check = False

Dim Rl, Cl, Rr, Cr, i As Integer

Rl = lookupArray.Rows.Count
Cl = lookupArray.Columns.Count
Rr = resultsArray.Rows.Count
Cr = resultsArray.Columns.Count

‘ lookupType is type of lookup. 0 = get first exact value, 1 = get last exact value, 2 = get nearest value, 3 = get nearest value below, 4 = get nearest value over, 5 = return “” if not found, 6 = interpolate

Select Case lookupType
Case 0 ‘ get first exact value
For i = 1 To Rl
If check = False Then
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
check = True
End If
End If
Next
If temp = “error” Then
temp = “value not found”
End If

Case 1 ‘ get last exact value
For i = 1 To Rl
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
End If
Next
If temp = “error” Then
temp = “value not found”
End If

Case 2 ‘ get nearest value
For i = 1 To Rl
If i = 1 Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
Next

Case 3 ‘ get nearest value below
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) <= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
End If
Next

Case 4 ‘get nearest value over
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) >= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
End If
Next

Case 5 ‘linear interpolation
check = False
temp1 = 0
temp2 = 0
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) <= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
Ra = Val(resultsArray.Cells(i, 1))
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
Ra = Val(resultsArray.Cells(i, 1))
End If
End If
End If
Next
a = temp1
check = False
temp1 = 0
temp2 = 0
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) >= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
Rb = Val(resultsArray.Cells(i, 1))
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
Rb = Val(resultsArray.Cells(i, 1))
End If
End If
End If
Next
b = temp1
If a = b Then
temp = Ra
Else
temp = ((value – a) / (b – a)) * (Rb – Ra) + Ra
End If

Case 6 ‘return “” if not found
temp = “”
For i = 1 To Rl
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
End If
Next

End Select

Ulookup = temp

End Function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s