Excel – Calculations on Filtered Data

[Pages:1]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.

Google Online Preview   Download