Excel custom functions

Sometimes, when writing formulas, you cannot find the appropriate Excel built-in functions, or you can use the built-in functions, but the formulas are complicated and difficult to understand, in this case, you can consider using udfs.
Writing a UDF requires a certain degree of VBA basics, but after completion, it can be as convenient as using a built-in function, anyone can use it. Tools/Raw Materials

• Excel

STEP/Method

1. The following is an example to learn how to write a UDF.

2. 
   For example, the area of some triangles needs to be calculated in the following table.

3. Column B is the bottom edge length, and column C is high. You must use the formula to calculate the Triangle Area in column D.
   

4. 
   (We usually use the formula = B3 * C3/2 in cell D3 to calculate the formula, and drag and copy the formula below column D to obtain other formulas. This is just a simple example to learn how to compile simple user-defined functions)

5. 1. Open the VBA window
   Press Alt + F11 to call up the VBA window and insert a user module.
   
6. 2. write code
   Generally, a user-defined function starts with a function command. After this command, specify a name and a parameter for it.
   Paste the following UDF code to the newly inserted user module.
   Function sjxmj (Di, Gao)
   Sjxmj = di * GAO/2
   End Function

   This code is very simple with only three lines. First, let's look at the first line. sjxmj is the name of the function you have taken, and the parameters in the brackets are variables. Di indicates "bottom edge length ", gao indicates "high". Two parameters are separated by commas.
   Let's look at the second line. This is the calculation process. Assign the di * GAO/2 formula to sjxmj, that is, the name of the UDF.
   The third line is paired with the first line. When you manually enter the first line, the end function of the third line automatically appears, end of the custom function.

   
   
7. 3. Use User-Defined Functions
   Return to the Excel window and enter the formula = sjxmj (B3, C3) in cell D3 to get the Triangle Area of this row. The usage of this line is exactly the same as that of the built-in function.
   
8. The above example shows how to write and use a UDF. Next we will introduce a UDF with a slightly more complex point.

   Friends who often process data may encounter multiple conditions to find a data. In this case, you need to write an "array formula" to solve the problem. The formula is long and hard to understand.

   
   For example, the following table is used to calculate the number of students whose two functions are 90 or more points based on the A1: D7 score table.

   
   We can see that the formula in cell H3 is long and difficult to understand.
   We can also get the correct result through the custom function. The function code is as follows:

   Function statistics (A, B, C, D, E)
   For I = 1 to A. Rows. Count
   If B = A. cells (I, 1) and A. cells (I, c)> = E and A. cells (I, d)> = E then
   Statistics = statistics + 1
   End if
   Next
   End Function

   
   This function uses five parameters (because it involves a region and four conditions)
   Parameter A indicates the area to be counted. In this example, It is B2: E7.
   Parameter B indicates the class to be counted. In this example, it is a G3 cell.
   Parameter C indicates the number of columns to the right of the first column of the area relative to the mathematical score. In this example, It is 3.
   Parameter d indicates the number of columns to the right of the first column of the area relative to the mathematical score. In this example, the value is 4.
   Parameter e indicates the score. In this example, the score is 90.
   Note: The number of Columns with parameters C and D "relative" to "region" is not the right column starting from column.
   Paste the above Code into the user module.

   
   Return to the Excel window and enter formula = statistics ($ B $2: $ e $7, G3, 3,4, 90) in the H3 cell to display the correct result.

   Function statistics 2 (a, B)
   For I = 1 to A. Rows. Count
   If B = A. cells (I, 1) and A. cells (I, 3)> = 90 and A. cells (I, 4)> = 90 then
   Statistics = statistics + 1
   End if
   Next
   End Function

   
   Enter the formula = 2 ($ B $2: $ e $7, G3) in the H3 cell in the table.

   
   We can see from the above that a user-defined function can use "Chinese characters" as the name of the function to facilitate memory and simplify the parameters according to the actual situation.

9. In this case, the array formula = sum (if ($ B $2: $ B $7 = G3) * ($ d $2: $ d $7 >=90) * ($ e $2: $ e $7 >=90 ))
   
10. Tip:
    If the format of our score table is fixed, the score position of each subject is also fixed relative to the region, and the score to be calculated is also fixed to 90, you can reduce the number of parameters to two in a custom function, as shown below:
    

Experience is for reference only. If you need to solve specific problems (especially in legal, medical, and other fields), we recommend that you consult professionals in related fields in detail. Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------
Instance test:

Function clarity_sort(str)    array1 = Array("FL", "IF", "VVS1", "VVS2", "VS1", "VS2", "SI1", "SI2", "P1", "P2")    For i = 0 To UBound(array1)       If array1(i) = str Then          clarity_sort = i + 1          Exit For       End If    Next iEnd Function

Custom Functions

Instance application