Calc Guide

Function index Function
8 STDEVP
9 SUM
10 VAR
11 VARP
Tip
Do not forget that the SUBTOTAL function ignores cells that use
the SUBTOTAL function. Say you have a spreadsheet that tracks
investments. The retirement investments are grouped together
with a subtotal. The same is true of regular investments. You can
use a single subtotal that includes the entire range without
worrying about the subtotal cells.
Using formulas to find data
Calc offers numerous methods to find data in a sheet. For example,
Edit > Find & Replace moves the display cursor based on simple and
advanced searching. Use Data > Filter to limit what is displayed
rather than simply moving the cursor. Calc also offers lookup functions
used in formulas, for example a formula to look up a student’s grade
based on their test score.
Search a block of data using VLOOKUP
Use VLOOKUP to search the first column (columns are vertical) of a
block of data and return the value from another column in the same
row. For example, search the first column for the name “Fred” and
then return the value in the cell two columns to the right. VLOOKUP
supports two forms:
VLOOKUP(search_value; search_range; return_column_index)
VLOOKUP(search_value; search_range; return_column_index;
sort_order)
The first argument, search_value, identifies the value to find. The
search value can be text, a number, or a regular expression. For
example, Fred searches for the text Fred, 4 searches for the number 4,
and F.* is the regular expression for finding something that starts with
the letter F.
The second argument, search_range, identifies the cells to search;
only the first column is searched. For example, B3:G10 searches the
same sheet containing the VLOOKUP formula and Sheet2.B3:G10
searches the range B3:G10 on the sheet named Sheet2.
Chapter 13 Calc as a Simple Database 379