Microsoft Office Tutorials and References
In Depth Information
Summarizing worksheets by using hidden and filtered rows
The RANDBETWEEN function generates a random whole number within a defined range.
For example, the formula =RANDBETWEEN(1,100) would generate a random integer value
from 1 to 100, inclusive. The RANDBETWEEN function is very useful for creating sample
data collections for presentations. Before the RANDBETWEEN function was introduced,
you had to create formulas that added, subtracted, multiplied, and divided the results of
the RAND function, which are always decimal values between 0 and 1, to create your data.
Summarizing worksheets by using hidden and filtered rows
The ability to analyze the data that’s most vital to your current needs is important, but
there are some limitations to how you can summarize your filtered data by using functions
such as SUM and AVERAGE . One limitation is that any formulas you create that include the
SUM and AVERAGE functions don’t change their calculations if some of the rows used in the
formula are hidden by the filter.
5
Excel provides two ways to summarize just the visible cells in a filtered data list. The first
method is to use AutoCalculate. To use AutoCalculate, you select the cells you want to
summarize. When you do, Excel displays the average of the values in the cells, the sum of the
values in the cells, and the number of visible cells (the count) in the selection. You’ll find the
display on the status bar at the lower edge of the Excel window.
When you use AutoCalculate, you aren’t limited to finding the sum, average, and count of
the selected cells. To display the other functions you can use, right-click the status bar and
select the function you want from the shortcut menu. If a check mark appears next to a
function’s name, that function’s result appears on the status bar. Clicking a selected function
name removes that function from the status bar.
AutoCalculate is great for finding a quick total or average for filtered cells, but it doesn’t
make the result available in the worksheet. Formulas such as =SUM(C3:C26) always
consider every cell in the range, regardless of whether you hide a cell’s row by right-clicking
the row’s header and then clicking Hide, so you need to create a formula by using either
the SUBTOTAL function or the AGGREGATE function to summarize just those values that are
visible in your worksheet. With the SUBTOTAL function, you can summarize every value in a
range or summarize only those values in rows you haven’t manually hidden. The SUBTOTAL
function has this syntax: SUBTOTAL(function_num, ref1, ref2, ...) . The function_num argument
holds the number of the operation you want to use to summarize your data. (The operation
 
Search JabSto ::




Custom Search