The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
The SUBTOTAL function syntax has the following arguments:
Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
(includes hidden values)
(ignores hidden values)
- Ref1 Required. The first named range or reference for which you want the subtotal.
- Ref2,… Optional. Named ranges or references 2 to 254 for which you want the subtotal.
- If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
- For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
- The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
- The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
- If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.
A very important feature in Excel is the formula. It is used to calculate values based on what is in cells, perform operations on a cell content, fetch values after an operation based on your search criteria and much more.
Mathematical Formulas in Excel are used to perform various arithmetic operations like sum, average, count, max, min etc. Here is a list of most frequently used mathematical formulas in excel.
This function is used to adds all the values within a cell range.
sum(cell address : cell address)
In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function is used to add the values contained in a range of cells. A list of commonly used statistical functions is shown in Table1 “Commonly Used Statistical Functions”. Functions are more efficient than formulas when you are applying a mathematical process to a group of cells. If you use a formula to add the values in a range of cells, you would have to add each cell location to the formula one at a time. This can be very time-consuming if you have to add the values in a few hundred cell locations. However, when you use a function, you can highlight all the cells that contain values you wish to sum in just one step. This section demonstrates a variety of statistical functions that we will add to the Personal Budget workbook. In addition to demonstrating functions, this section also reviews percent of total calculations and the use of absolute references.
|ABS||The absolute value of a number|
|AVERAGE||The average or arithmetic mean for a group of numbers|
|COUNT||The number of cell locations in a range that contain a numeric character|
|COUNTA||The number of cell locations in a range that contain a text or numeric character|
|MAX||The highest numeric value in a group of numbers|
|MEDIAN||The middle number in a group of numbers (half the numbers in the group are higher than the median and half the numbers in the group are lower than the median)|
|MIN||The lowest numeric value in a group of numbers|
|MOD||The number that appears most frequently in a group of numbers|
|PRODUCT||The result of multiplying all the values in a range of cell locations|
|SQRT||The positive square root of a number|
|STDEV.S||The standard deviation for a group of numbers based on a sample|
|SUM||The total of all numeric values in a group|
2 thoughts on “Subtotal, Mathematical functions, Statistical functions”