In This tutorial we learn that how we create User define function in Excel with very simple example. For example if you have student exam record and you want to calculate the Percentage of the students then in excel there is no proper pre define function . you can calculate the percentage of student using manual formula like that
=marks obtained * 100 / total marks
but you can also create your own function for calculating percentage and you can use it as pre define function.
Note:- In this tutorial basically I want to describe the Process of creation of UDF function and how we use it. For this purpose I am going to create a new UDF function with the Name Percentage and then I want to use this function. These are the basic steps. You can create more function according to your needs.
First of all add the Developer Manu tab in Excel ribbon. For this purpose click on office button and click on Excel Option button.
Make active the check box "Show the developer " Tab in ribbon".
Now in excel environment you can see an extra tab "Developer" in excel ribbon.
Now click on This Developer Tab. A new window is open in Visual Basic Environment.
Click on Insert menu.
Select Module.
Now write your function in module window see the image below:
Make shore that General option is selected as shown above image with red circle.
Note: you just copy the following code for time being to learn the process:
Function percentage(tm As Double, mo As Double) As Double
percentage = (mo * 100) / tm
End Function
In this function percentage two variable are pass as an argument. one is tm which store the total marks and mo store the Marks obtained.
Now save this file as Excel add-in file . for this purpose click on save button and select the file type as Excel add-in file. Give the any name and click on save button.
Next add this Add-in sport in MS excel. So that you can use this Function any workbook on you system.
For this click on office button and click on Excel Option button.
Select Add-in from right menu in Appearing window.
Click on Go button in The bottom.
Now click on Brows button and select the Add-in file in which you have your own UDF (User define function).
Ok The first phase of Creation of UDf is complete. Now Below we discus
Open new excel workbook and create a simple table which have four field such that Name Total marks , Marks Obtained and Percentage.
Now Select the cell E4 ( where you want to calculate the Percentage) Click on Formula Tab and Select select Insert Function.
Select User Define function from the category list and then select your Function in my case function is percentage.
Now give the TM as C4 which is total marks and MO as D4 which is Marks Obtained.
Press enter or click on ok button.
Don.
PMT() Financial Functions
Calculate the Sum of a Range Using AutoSum function
User Define Function (UDF) In Excel
Macros In Excel
Relative and Absolute Cell Reference in MS Excel
Page Setting and printing In MS Excel
Fill In Excel
How we use The Functions in MS Excel
How we Write Excel Formulas
Components of Excel 2007 Environment
Rounding The Numbers MS Excel
SUMIF FUNCTION In MS EXCEL
COUNTIF Function In MS Excel
COUNTBLANK Function In MS Excel
VLOOKUP Function In MS Excel
Pivot Table In MS Excel
Consolidation In MS Excel
Remove Duplicate Value In MS Excel
Convert Excel Data in to a Table