Custom Function or UDF (user defined functions)-1

Refrence from office.microsoft.com:

Creating functions

If you have a complex calculation that you reuse frequently in Excel, you don't have to repeatedly enter a 

long, complex worksheet formula. Instead, you can create your own worksheet function to perform the 

calculation. You can then use the function to create formulas that are easier to enter and maintain.

To create your own custom functions, you work in Microsoft Visual Basic® for Applications (VBA). VBA is

 a programming language that's built into Excel. VBA is very flexible and can do everything that Excel 

formulas can do, and more.

For example, say you have a complex formula for figuring sales commissions, where the commission 

percentage changes based on several factors such as what products and mix of products a representative

 sells, whether the sales rep is under quota, and the total sales for the quarter. Instead of entering the 

lengthy formula that takes all of these factors into account every time you want to calculate the 

commission for a sale, you can create a custom commission function. Then all you have to type in your commission formulas is the name of your function.

Furthermore, the next time your company changes the rules for how commissions are calculated, you don't have to find and make tedious changes to all of your complex formulas. You only have to make changes in one place, the custom function, to update all the formulas in your workbook.

The following steps show how to create and use a custom function. To keep the example simple, this

 function calculates a sales commission at a flat 6% rate. To take full advantage of a custom function, 

you would substitute more complex VB code for this simple percentage calculation.

On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu in the Microsoft Visual Basic window, click Module.
In the Module window, type the code for your function. For example, a function to calculate a 6% sales commission might look like this:

The first line in the example above creates a function named Commission, which performs its calculations

 on a number or cell reference. The function uses a variable, MyNum, to store the number or value from 

the cell. The second line calculates the value of the Commission function by multiplying the number or cell reference (MyNum) by 0.06 (a more sophisticated function could have several lines of code to perform its calculations). The third line ends the function code.

On the File menu, click Close and Return to Microsoft Excel.
On the worksheet, use your function in formulas as you would any worksheet function. For example, you could use the Commission function to calculate the commissions on sales:

A function that you create in this manner is available for use only in the workbook where you create it. 

If you want to use the function elsewhere, you can copy the code for the function to VBA modules in 

other workbooks; or, if you're a developer, you can copy the function into a workbook used as a function

 library and compile that workbook as an add in program.