Excel allows you to create your own “User-Defined Functions” that can be used the same way as any other built-in function in Excel, i.e. IF, SUM, VLOOKUP, etc. With a little knowledge of Visual Basic Applications (VBA) code, you can create your own function to meet your needs.
As an example of how to create a User-Defined Function, let’s create a function that calculates fuel consumption in miles per gallon.
- Open a new Excel workbook.
- Open VBA (Alt + F11)
- Right click the workbook’s name, i.e. VBAProject (Book1) and select Insert | Module
In order to calculate your vehicle’s fuel consumption in miles per gallon, you’ll need to know a couple things:
- How many miles driven: ending miles minus beginning miles
- Number of gallons used Miles per Gallon = (Ending Miles – Beginning Miles) / Gallons Used
Now, from the new module you opened in VBA, begin typing your function as follows.
Function MPG(BeginningMiles As Integer,
EndingMiles As Integer, Gallons As Single)
MPG = (EndingMiles - BeginningMiles) / Gallons
After entering the User-Defined Function in your module, go back to the Excel workbook and test your new function. This function works the same way any built in function would work.