Tiêu đề Excel-Formulas-Bible.pdf ✅

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