Utility Lib contains methods which can be used like utility methods

EXCEL Formulas Bible Excel 2013 / 2016 Table of Contents Table of Contents Contents

1. SUM of Digits when cell Contains all Numbers ...................................................................................... 1
2. SUM of Digits when cell Contains Numbers and non Numbers both .......................................... 1
3. A List is Unique or Not (Whether it has duplicates) ........................................................................... 1
4. Count No. of Unique Values ............................................................................................................................. 1
5. Count No. of Unique Values Conditionally ............................................................................................... 1
6. Add Month to or Subtract Month from a Given Date .......................................................................... 2
7. Add Year to or Subtract Year from a Given Date ................................................................................... 2
8. Convert a Number to a Month Name .......................................................................................................... 3
9. Converting Date to a Calendar Quarter ..................................................................................................... 3
10. Converting Date to a Indian Financial Year Quarter ...................................................................... 3
11. Calculate Age from Given Birthday ......................................................................................................... 4
12. Number to Date Format Conversion ...................................................................................................... 4
13. Number to Time Format Conversion ..................................................................................................... 4
14. Count Cells Starting (or Ending) with a particular String ............................................................ 4
15. Count No. of Cells Having Numbers Only ............................................................................................. 5
16. Count No. of Cells which are containing only Characters ............................................................ 5
17. Number of Characters in a String without considering blanks ................................................. 5
18. Number of times a character appears in a string ............................................................................. 5
19. Count Non Numbers in a String ................................................................................................................ 5
20. Count Numbers in a String .......................................................................................................................... 6
21. Count only Alphabets in a String .............................................................................................................. 6
22. Most Frequently Occurring Value in a Range .................................................................................... 6
23. COUNTIF on Filtered List ............................................................................................................................. 6
24. SUMIF on Filtered List ................................................................................................................................... 7
25. Extract First Name from Full Name ........................................................................................................ 7
26. Extract Last Name from Full Name ......................................................................................................... 7
27. Extract the Initial of Middle Name .......................................................................................................... 7
28. Extract Middle Name from Full Name ................................................................................................... 7
29. Remove Middle Name in Full Name ....................................................................................................... 7
30. Extract Integer and Decimal Portion of a Number .......................................................................... 8
31. First Day of the Month for a Given Date ............................................................................................... 8
32. How Many Mondays or any other Day of the Week between 2 Dates ................................... 8
33. Maximum Times a Particular Entry Appears Consecutively ...................................................... 9
34. Find the Next Week of the Day .................................................................................................................. 9
35. Find the Previous Week of the Day ...................................................................................................... 10
36. Get File Name through Formula ............................................................................................................ 10
37. Get Workbook Name through Formula ............................................................................................. 11
38. Get Sheet Name through Formula ........................................................................................................ 11
39. Get Workbook's Directory from Formula ......................................................................................... 11
40. Last Day of the Month for a Given Date ............................................................................................. 11
41. Perform Multi Column VLOOKUP ......................................................................................................... 12
42. VLOOKUP from Right to Left ................................................................................................................... 13
43. Case Sensitive VLOOKUP .......................................................................................................................... 13
44. Rank within the Groups ............................................................................................................................. 14
45. Remove Alphabets from a String .......................................................................................................... 14
46. Remove numbers from string................................................................................................................. 15
47. Roman Representation of Numbers .................................................................................................... 15
48. Sum Bottom N Values in a Range .......................................................................................................... 15
49. Sum Every Nth Row ..................................................................................................................................... 16
50. We have AVERAGEIF. What about MEDIANIF and MODEIF? ................................................. 16
51. Number of Days in a Month ..................................................................................................................... 17
52. How to Know if a Year is a Leap Year ................................................................................................. 17
53. Last Working Day of the Month If a Date is Given ........................................................................ 17
54. First Working Day of the Month if a Date is Given ....................................................................... 18
55. Date for Nth Day of the Year ................................................................................................................... 18
56. Calculate Geometric Mean by Ignoring 0 and Negative Values .............................................. 19
57. Financial Function - Calculate EMI ....................................................................................................... 19
58. Financial Function - Calculate Interest Part of an EMI ............................................................... 20
59. Financial Function - Calculate Principal Part of an EMI ............................................................ 22
60. Financial Function - Calculate Number of EMIs to Pay Up a Loan ........................................ 23
61. Financial Function - Calculate Interest Rate ................................................................................... 24
62. Financial Function – Calculate Compounded Interest ................................................................ 25
63. Financial Function – Calculate Effective Interest .......................................................................... 26
64. Abbreviate Given Names ........................................................................................................................... 27
65. Get Column Name for a Column Number .......................................................................................... 28
66. Get Column Range for a Column Number ......................................................................................... 29
67. Find the nth Largest Number when there are duplicates ......................................................... 29
68. Extract Date and Time from Date Timestamp ................................................................................ 30
69. Convert a Number into Years and Months ....................................................................................... 30
70. COUNTIF for non-contiguous range .................................................................................................... 31
71. Count the Number of Words in a Cell / Range ............................................................................... 31
72. Numerology Sum of the Digits aka Sum the Digits till the result is a single digit .......... 32
73. Generate Sequential Numbers and Repeat them .......................................................................... 32
74. Repeat a Number and Increment and Repeat.... ............................................................................ 32
75. Generate Non Repeating Random Numbers through Formula .............................................. 33
76. Financial Year Formula (e.g. 2015-16 or FY16) ............................................................................ 34
77. First Working Day of the Year ................................................................................................................ 34
78. Last Working Day of the Year ................................................................................................................. 34
79. Convert from Excel Date (Gregorian Date) to Julian Date ........................................................ 35
80. Convert from Julian Dates to Excel (Gregorian) Dates ............................................................... 35
81. Extract User Name from an E Mail ID ................................................................................................. 36
82. Extract Domain Name from an E Mail ID .......................................................................................... 36
83. Location of First Number in a String ................................................................................................... 36
84. Location of Last Number in a String .................................................................................................... 36
85. Find the Value of First Non Blank Cell in a Range......................................................................... 36
86. Find First Numeric Value in a Range ................................................................................................... 36
87. Find Last Numeric Value in a Range .................................................................................................... 36
88. Find First non Numeric Value in a Range ......................................................................................... 37
89. Find Last non Numeric Value in a Range .......................................................................................... 37
90. Find Last Used Value in a Range ........................................................................................................... 37
91. MAXIF ................................................................................................................................................................. 37
92. MINIF .................................................................................................................................................................. 37
93. Generate a Unique List out of Duplicate Entries ........................................................................... 38

https://media-exp1.licdn.com/dms/document/C4E1FAQEVyC5hjFNzgw/feedshare-document-pdf-analyzed/0/1641125387013?e=1641448800&v=beta&t=KDO1lo1QqFUadgyDM7Pn1Nux5BEiBsRbhLdzilxlXw0