Excel – Calculations on Filtered Data
Excel ? Calculations on Filtered Data
When you filter your worksheet, formulas you have created don't change their calculations, even if some of the rows used in the formula are hidden by the filter. You can use AutoCalculate to quickly view calculations relevant to the filtered data. You also can insert simple formulas into a worksheet using the AutoSum button on the Standard toolbar.
There are two ways you can find the total of a group of filtered cells. The first method is to temporarily view the subtotal for selected cells in the status bar at the bottom of the Excel window. To do this, you'll select the cells you want to find the total for, and the total for those cells appears in the AutoCalculate pane on the right side of the status bar.
When you use AutoCalculate, you aren't limited to finding the sum of the selected cells. To display the other functions you can use, point to the AutoCalculate pane, press down the right mouse button, and in the shortcut menu that appears, use the left mouse button to select a function.
AutoCalculate is great for finding a quick total or average for filtered cells, but it doesn't make the result available in the worksheet. To make the value available in your worksheet, you can create a SUBTOTAL function. As with AutoCalculate, you can choose the type of calculation the function performs.
The SUBTOTAL function is expressed as the formula SUBTOTAL=(Function_Num, Ref1, Ref2, ...) .
In this formula, the function number (Function_Num) tells Excel what type of calculation will be made within the SUBTOTAL function. The following table lists the most common function numbers and their corresponding functions.
Function Number
1 2 4 5 6 9
Function Name
AVERAGE COUNT MAX MIN PRODUCT SUM
Description
Returns the average (arithmetic mean) of the arguments. Counts the number of cells that contain numbers and also numbers within the list of arguments. Returns the largest number in a set of values. Returns the smallest number in a set of values. Multiplies all the numbers given as arguments and returns the product. Adds all the numbers in a range of cells.
You can also use the AutoSum button to insert functions other than the SUBTOTAL function by clicking the AutoSum button down arrow. Doing so displays a list of functions to choose from. However, if you choose a function from this list, it will not be implemented from within the SUBTOTAL function, so Excel will calculate using all of the values in a given range, rather than only those that are visible within a filtered range.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- expected value mean and variance using excel
- using excel to find perimeter area volume lbs4
- service computation date scd calculation
- excel calculations on filtered data
- measuring evaluation results with microsoft excel
- how to run statistical tests in excel
- easy calculations in excel github pages
- analyzing your qrt
Related searches
- microsoft excel calculations formulas
- sum on excel based on specific word
- excel return on investment template
- excel 2016 stock quote data connection
- free excel tutorials on youtube
- excel percentiles for large data set
- excel calculations examples
- color coding in excel based on values
- best excel training on youtube
- water softener calculations based on hardness
- excel vba turn off calculations during macro
- cagr in excel based on population size