Advanced MS Office & AI Tools

  Detailed Process Flow to Use the YEARFRAC Function in Excel What is the YEARFRAC Function? The YEARFRAC function in Excel calculates the fraction of the year between two dates. It is useful for: ✅ Calculating age from the date of birth (as seen in the image). ✅ Determining the exact number of years between two dates. ✅ Financial calculations like interest accruals or service periods. Syntax of the YEARFRAC Function  =YEARFRAC(start_date, end_date, [basis]) start_date → The beginning date (e.g., Date of Joining). end_date → The ending date (e.g., TODAY() for current age). [basis] (optional) → Determines the method of calculation. Default is 0 (US 30/360). Basis Description 0 US (NASD) 30/360 1 Actual/Actual 2 Actual/360 3 Actual/365 4 European 30/360 To calculate age, we use basis 1 (Actual/Actual). Step-by-Step Process to Use YEARFRAC in Excel Step 1: Select the Cell for the Age Calculation Click on the first cell in the "Age" column where the result should appear (e...

  Detailed Process Flow to Use the COUNTA Function in Excel What is the COUNTA Function? The COUNTA function in Excel is used to count the number of non-empty cells in a given range. It counts: ✅ Numbers ✅ Text ✅ Logical values (TRUE/FALSE) ✅ Errors and formulas returning a value However, it does not count blank cells. Purpose of Using the COUNTA Function To count the total number of non-empty entries in a dataset. To determine the number of filled cells in a column. To verify data completeness (checking missing values). Syntax of the COUNTA Function  =COUNTA(range) range → The group of cells to count. Step-by-Step Process to Use COUNTA Function in Excel Step 1: Select the Cell for the Result Click on the cell where you want to display the count. (In the given image, the result is displayed in D16 under "CountA"). Step 2: Enter the COUNTA Formula To count the total number of non-empty rows in Column A (SL numbers):  =COUNTA(A2:A15) or To count the total number of ...

  Detailed Process Flow to Use the RANK Function in Excel What is the RANK Function? The RANK function in Excel assigns a rank to a number within a list, based on its value relative to other numbers. It is useful for ranking students, employees, or competitors based on scores, percentages, or other numerical values. Purpose of Using the RANK Function To quickly rank numerical values (e.g., percentages, scores, sales figures). To analyze performance by identifying top and bottom ranks. To sort and compare values without manually reordering data. Benefits of the RANK Function ✅ Saves Time – Automates the ranking process. ✅ Accurate & Dynamic – Updates ranks automatically when values change. ✅ Flexible – Can rank in ascending or descending order. Syntax of the RANK Function  =RANK(number, ref, [order]) number → The value to rank. ref → The range of values. order → 0 for descending (highest rank = 1), 1 for ascending (lowest rank = 1). Step-by-Step Process to Use R...

  Detailed Process Flow to Use the MOD Function in Excel What is the MOD Function? The MOD function in Excel returns the remainder after dividing one number by another. It is mainly used to separate integer and decimal values, cycle through repeating sequences, or perform time-related calculations. Purpose of Using the MOD Function To extract the decimal part of a number. To handle time calculations , such as converting decimal hours into standard time format (HH:MM). To cycle through periodic sequences , like assigning shifts or processing recurring patterns. Benefits of the MOD Function ✅ Extracts Decimal or Remainder Values – Helps in breaking down numbers into useful parts. ✅ Simplifies Time Calculations – Converts decimal hours into minutes easily. ✅ Helps in Repeating Patterns – Useful in cyclic processes like scheduling. ✅ Enhances Formatting of Time Data – Useful for making work hours more readable. Syntax of the MOD Function  =MOD(number, divisor) number → The ...

  Detailed Process Flow to Use the ABS Function in Excel What is the ABS Function? The ABS (Absolute Value) function in Excel returns the absolute (non-negative) value of a number. It converts negative numbers into positive ones while leaving positive numbers unchanged. Purpose of Using the ABS Function To remove negative signs from values while keeping their magnitude. To calculate absolute differences in datasets where only positive values are needed. To standardize data when dealing with values that should always be positive. Benefits of the ABS Function ✅ Removes Negative Values – Ensures that all numbers are positive. ✅ Maintains Data Integrity – Keeps values unchanged except for their sign. ✅ Useful for Calculations – Especially helpful in distance, deviation, and variance calculations. ✅ Prevents Errors in Comparisons – Some calculations require only positive values. Syntax of the ABS Function excel Copy Edit =ABS(number) number → The value or cell reference from wh...

  Detailed Process Flow to Use the SORTBY Function in Excel What is the SORTBY Function? The SORTBY function in Excel sorts a range or array based on values in a corresponding range or array. Unlike the traditional SORT function, SORTBY allows sorting based on another column while keeping the original dataset intact. Purpose of the SORTBY Function To sort data dynamically based on one or more columns. To avoid altering the original dataset while still organizing data efficiently. To apply multi-level sorting based on various criteria such as highest working days, department names, or employee codes. Benefits of Using SORTBY Function ✅ Dynamic Sorting – When data updates, the sorting automatically adjusts. ✅ Maintains Original Data – Unlike manually sorting, this function keeps the raw dataset unchanged. ✅ Multi-Level Sorting – You can sort by multiple criteria (e.g., working days first, then department). ✅ Eliminates Manual Work – Reduces the need for frequent manual re-so...