PK’s function Kit is an Excel Add-in which have 18 user defined functions. It will your increase your productivity and will make your daily work easy.
How to install:
After downloading this addin you need to install it. Below are the steps to install it.
- Open a new workbook.
- Go to File>>Option or press (Alt+T+O)
- Click on Add-Ins.
- Select Excel Add-ins in Manage drop down.
- Click on Go.
- Add-Ins window will be opened.
- Click on Browse.. button.
- Select PK’s Function Kit V1.0 add-in file.
Add-in will be installed.
Below are the functions list available in this function kit.
S.No. Function Name Syntax Help Text
1 PK_Unique_Count criteria_Range This Formula will return the unique values count from given criteria range. Criteria Range should not have more than 100000 rows.
2 PK_Duplicate_Count criteria_Range This Formula will return the duplicate values count from given criteria range. Criteria Range should not have more than 100000 rows.
3 PK_Vlookup_By_Cell_background_Color Lookup_Cell As Range, Table_Array As Range, Col_Num As Integer, Get_last_Value As Boolean This formula will return the value from Table array range on the base of lookup cell background color. Col_index_number to be given as number of the column in table array from which it will return the value. Get_last_Value is Boolean, if there are duplicate colors in table array then for 0 it will return the first value from table array and for 1 it will return the last value from table array. Note: This formula will not refreshed automatically when color is changed in table arrary, need to press F9. This will not work with conditional formatting colors.Table Array range should not have more than 100000 rows.
4 PK_Vlookup_By_Partial_Lookup_Value lookup_value As Variant, Table_Array As Range, col_index_num As Integer, range_lookup As Boolean This formula will work just like VLOOKUP. First it will check the complete lookup value and will return the result. If complete lookup value is not available in table array then it will check if lookup value is available partially then it will return the value.
5 PK_Count_By_Cell_background_Color criteria_Range As Range, criteria_cell As Range This formula will return the count on the base of criteria cells backgound color. Note: This formula will not refreshed automatically when cell backgound color is changed, need to press F9. This will not work with conditional formatting colors.criteria_Range should not have more than 100000 rows.
6 PK_Count_By_Case_Sensitive_Criteria criteria_Range As Range, criteria As String This formula will return the count on the base of criteria value. It will count only when case of criteria is matched in criteria range. For Example: if criteria is "ABC" then it will not count "abc" from criteria range. Note: criteria_Range should not have more than 100000 rows.
7 PK_Count_Font_Bold_Cells criteria_Range This formula will return the count on the base of criteria cells's font is bold. Note: This formula will not refreshed automatically when font is made bold in criteria range, need to press F9. This will not work with conditional formatting font bold .criteria_Range should not have more than 100000 rows.
8 PK_Count_By_Partial_Value criteria_Range As Range, criteria As String This formula will return the count on the base of criteria value. It will also count when criteria is partially available in criteria range. For Example: if criteria is "ABC" then it will not count "abc" from criteria range. Note: criteria Range should not have more than 100000 rows.
9 PK_Text_Join delimiter As String, ignore_empty As Boolean, text_range As Range This formula will join the multiple strings given in text range separated by the given delimiter. Ignore_empty needs to be taken as true or false. Note: Text Range should not have more than 100000 rows.
10 PK_Extract_Numbers Alphanumeric_Text This formula will extract the numbers from Alphanumeric Text. It will return a string not a number
11 PK_Extract_Text Alphanumeric_Text This formula will extract the non numeric characters from Alphanumeric Text. It will return a string.
12 PK_Weighted_Average Weight_Range,Average_range This formula will return the weighted average for given Average range. Weight range and Average range should have the same number of rows.
13 PK_Sum_Of_Digits Whole_Number This formula will return the sum of digits of given number.
14 PK_First_Name Complete_Name This formula will return the first name from the given complete name.
15 PK_Middle_Name Complete_Name This formula will return the middle name from the given complete name. If middle name is not available then it will return blank.
16 PK_Last_Name Complete_Name This formula will return the last name from the given complete name. If last name is not available then it will return blank.
17 PK_Ordinal_Date_Format Input_Date This formula will return the ordinal date for given date like for 1/1/2018 it will change as 1st Jan, 2018. Note: It will return a string not a date.
18 PK_Help Formula_Name This formula will return the help text of given formula.