SUBTOTAL Function in Excel (103 functions).

SUBTOTAL Function or 103 functions in Excel: Your go-to resource for streamlined data analysis. Whether you’re calculating sums, averages, or counts, this versatile tool simplifies your tasks. With the power of 103 functions at your fingertips, you can efficiently analyze your data, make informed decisions, and excel in your work. Say goodbye to manual calculations and embrace the efficiency of Excel’s SUBTOTAL function and its extensive functionality. It’s your key to unlocking the full potential of your data analysis endeavors.

This content covers:

1. What is 101 Excel Functions?
2. What is the Excel VBA 103 – Calling Add-In Functions?
3. What is the MOD Function?
4. How to use the MOD Function in Excel?

1. 101 Excel Functions.

Microsoft Excel has many functions to perform various calculations and tasks.

Here are 101 Excel functions with brief descriptions:

1. SUM: It Adds all numbers in a range.
2. AVERAGE: Calculate the average of numbers in a range.
3. COUNT: Counts the number of cells in a range that contain numbers.
4. MIN: It Returns the smallest value in a range.
5. MAX: Returns the maximum value in a range.
6. IF: Returns one value if the condition is true and another value if the condition is false.
7. VLOOKUP: Searches for a value in an array and returns the match.
8. HLOOKUP: Looks for a value in the first row of a table and returns the matching value in the same column.
9. INDEX: Returns the value of a cell in a specified row and column of a range.
10. MATCH: Finds a value in a range and returns its position.
11. CONCATENATE: Combine multiple text strings into one.

 12.TEXT: Converts a number to text with the specified format.

13. LEFT: Returns the leftmost characters of a text string.
14. RIGHT: Returns the rightmost character of the text string.
15. MID: Returns a specified number of characters from the middle of a text string.
16. LEN: Returns the length of the text string.
17. SUBSTITUTE: Replaces occurrences of a substring in a text string with another substring.
18. UPPER: Convert text to uppercase.
19. LOWER: Convert text to lowercase.
20. PRIVATE: Capitalize the first letter of each word.
21. TRIM: Remove extra spaces from text.
22. NOW: Returns the current date and time.
23. TODAY: Returns the current date.
24. YEAR: Returns the year of a date.
25. MONTH: Returns the month of a date.
26. DAY: Returns the day of a date.
27. DATE: Creates a date based on year, month, and day values.
28. TIME: Creates a time based on hour, minute, and second values.
29. DATE: Returns a date with the specified number of months before or after a given date.
30. EOMONTH: Returns the last day of the month after a specified number of months.
31. NETWORK DATE: Calculate the number of working days between two dates.
32. WEEKDAY: Returns the weekday of a date.
33. RAND: Generates a random number between 0 and 1.
34. RANDBETWEEN: Generates a random number between specified minimum and maximum values.
35. ROUND: Rounds a number to the specified number of decimal places.
36. FLOOR: Rounds a number to the nearest specified multiple.
37. CEILING: Rounds a number to the nearest specified multiple.
38. ABS: Returns the absolute value of a number.
39. SQRT: Calculate the square root of a number.
40. EXP: Returns the value of “e” raised to the power.
41. LOG: Calculate the logarithm of a number.
42. MOD: Returns the remainder of the division.
43. PI: Returns the value of pi (π).
44. SIN: Returns the sine of an angle.
45. COS: Returns the cosine of an angle.
46. TAN: Returns the tangent of an angle.
47. ASIN: Returns the arcsine of a number.
48. ACOS: Returns the cosine arc of a number.
49. ATAN: Returns the arctangent of a number.
50. DEGREES: Convert radians to degrees.
51. RADIANS: Convert degrees to radians.
52. CONCAT: Combine multiple text strings into one.
53. AND: Returns TRUE if all arguments are TRUE.
54. OR: Returns TRUE if an argument is TRUE.
55. NOT: Returns the opposite of a logical value.
56. IFERROR: Returns a value if the formula generates an error, otherwise returns the result of the formula.
57. COUNTIF: Counts the number of cells that meet a condition.
58. SUMIF: Add cells that meet a condition.
59. AVERAGEIF: Calculates the average value of cells that meet a condition.
60. COUNTIFS: Counts the number of cells that meet more than one condition.
61. SUMIFS: Add cells that meet multiple conditions.
62. AVERAGE: Calculates the average of cells that meet more than one condition.
63. DGET: Returns a unit
64. DCOUNT: Counts the number of records that meet the specified criteria in the database.
65. DSUM: Adds values ​​from a field that meets the specified criteria to the database.
66. DAVERAGE: Calculate the average of the values ​​that meet the specified criteria in the database.
67. DMAX: Returns the maximum value of a field that meets the specified criteria in the database.
68. DMIN: Returns the minimum value from a field that meets specified criteria in a database.
69. PRODUCT: Multiplies the values in a field that meet specified criteria in a database.
70. DSTDEV: Calculate the overall standard deviation of values ​​that meet the specified criteria in the database.
71. DSTDEVP: Calculates the sample standard deviation of values ​​that meet specified criteria in the database.
72. VAR: Calculate the overall variance of a series of numbers.
73. VARP: Calculate the sample variance of a series of numbers.
74. COVAR: Calculate the covariance between two series of numbers.
75. CORREL: Calculates the correlation coefficient between two ranges of numbers.
76. GROWTH: Calculates predicted exponential growth.
77. TREND: Calculates values along a linear trendline.
78. PREDICTION: Predict a value based on linear regression.
79. INTERCEPT: Calculate the intersection of a linear equation.
80. SLOPE: Calculates the slope of a linear equation.
81. LINEST: Calculates various statistics for a best-fit straight line.
82. FREQUENCY: Calculates how often values occur within a range.
83. RANK: Returns the rank of a value in a list.
84. PERCENTILE: Returns the kth percentile of values ​​in a range.
85. LARGE: Returns the kth largest value in the data set.
86. SMALL: Returns the kth smallest value in the data set.
87. NPER: Calculate the number of repayment periods for a loan.
88. PMT: Calculate the periodic payment of a loan.
89. IPMT: Calculate interest payments for a specific period of a loan.
90. PPMT: Calculates the principal payment for a specific period of a loan.
91. FV: Calculates the future value of an investment.
92. PV: Calculates the present value of an investment.
93. NPV: Calculate the net present value of a series of cash flows.
94. IRR: Calculate the internal rate of return of an investment.
95. MIRR: Calculate the modified internal rate of return for an investment.
96. XNPV: Calculate net present value based on specific dates.
97. XIRR: Calculate the internal rate of return based on specific dates.
98. SELECT: Returns a value from a list based on a specified position.
99. HYPERLINK: Create clickable hyperlinks.
100. CELL: Returns information about the cell’s format, position, or contents.

 101.INFO: Returns information about the current operating system and environment.

These functions include many mathematical, logical, text, date and time, statistical, financial, and other operations in Microsoft Excel. These are the important 101 excel functions.

2. What is the Excel VBA 103 – Calling Add-In Functions?

• The programming language known as Excel VBA enables tasks to be automated, and custom functions to write to and interact with data from Excel.

In addition to performing repetitive tasks, it is also commonly used for creating macros, custom add-ins, and automating Microsoft Excel tasks. Typically, these materials are divided into levels of beginner to intermediate and advanced education, but the terminology and course numbers may differ depending on the training organization or source.

It is important to ensure that the Excel VBA course or advanced-level training covers the topics and skills you are interested in, by carefully reviewing the course syllabus and prerequisites.

• Calling add-in functions in Excel is relatively simple, whether you use built-in Excel add-ins or custom add-ins that you have installed.

Add-ins are sets of additional features and functionality that can expand Excel’s capabilities.

3. What is the MOD Function?

The MOD function in Excel is a mathematical function that sends the remainder when one number (the dividend) is divided by another number (the divisor). It is repeatedly used to implement modular arithmetic or to determine whether a number is detachable by another number.

There are two MOD function syntax.

1. Number: This is the dividend, the number whose remainder you want to find.
2. Divisor: This is the number you want to divide to find the remainder.

The MOD function will give back the remainder of the division.

For say,

If you use the function =MOD(10,3),

It will return 1 because when 10 is divided by 3 the remainder is 1.

If you use the function =MOD(6,2)

It will return 0, indicating that 6 is even.

The MOD function is similarly used in many other calculations, as well as planning, cycle modeling, and more complex calculations in Excel.

4. How to use the MOD Function in Excel?

Step 1: Enter all the necessary data into your excel sheet as shown below.

Data are placed into the table.

Step 2: As you can see from the above picture there is no divisor so you have to divide these data among them.

For this, the formula will be: =MOD(A3,A4)

Formula is used here.

Step 3: Entered the formula and the result is 3, which is the remainder.

The result is outlined below.

Step 4:  Use the same formula for every data, the data you want to divide apply the formula based on that, and you will get your result.

The formula used here is:

=MOD(A6,A4) which has reminder 1.

=MOD(A3,A5) which has reminder 1.

=MOD(A6,A5) which has reminder 16.

=MOD(A5,A7) which has reminder -7.

You can see the result is outlined below:

Application of SUBTOTAL Function or 103 functions in Excel

1. Data Summarization: SUBTOTAL allows you to calculate various summary statistics, such as sum, average, count, and more, for a selected range of data. It’s handy for quick data summarization.
2. Filter-Dependent Calculations: SUBTOTAL functions are especially useful when working with filtered data. They automatically adapt to the visible data, excluding hidden rows, making them ideal for dynamic summaries.
3. Conditional Analysis: You can use functions like COUNTIF, SUMIF, and AVERAGEIF as part of SUBTOTAL to perform conditional calculations. For example, counting specific items that meet a condition within a filtered dataset.
4. Grouped Data Analysis: When working with grouped data or subtotals, SUBTOTAL can provide summary statistics for each group or subtotal level, giving you insights into different categories or sections of your data.
5. Non-Double Counting: When used correctly with functions like COUNT, SUBTOTAL ensures that you don’t double-count items when working with filtered or subtotaled data. It’s a great tool for accurate data analysis.
6. Nested Functions: You can nest multiple functions within a SUBTOTAL function to perform complex calculations. For instance, calculating the average of filtered data that meets specific criteria.

These functions, including SUBTOTAL, offer versatility and efficiency, making them indispensable tools for data analysis in Excel.

You may be interested:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts