These four functions are among Excel's Database and List
Management Functions. Where the functions calculate the variance or the
standard deviation over a set of observations, DVAR, DVARP, DSTDEV, and DSTDEVP
differ from VAR, VARP, STDEV, and STDEVP (respectively) in the way they define
the set of observations.
DVAR, DVARP, DSTDEV, and DSTDEVP take as
arguments a database, a field, and criteria. The functions take as observations
the field values in each database record that satisfies criteria.
VAR,
VARP, STDEV, and STDEVP take as arguments a set of up to 30 value arguments.
Most frequently, VAR, VARP, STDEV, and STDEVP use a single value argument that
corresponds to a range of cells, such as VAR(A1:B100).
Therefore,
with VAR, VARP, STDEV, and STDEVP, you specify the cells that contain the
values you want to include. With DVAR, DVARP, DSTDEV, and DSTDEVP, you specify
a database (table), field (column) and criteria. They include only those values
that occur in cells that satisfy the criteria.
Syntax
DVAR(database, field, criteria)
is the range of cells that makes up the list or database. A
database is a list of related data where the rows of related information are
records, and the columns of data are fields. The first row of the list contains
labels for each column.
Field indicates what column the function uses. The field argument can
appear as text with the column label enclosed between quotation marks, such as
"Age" or "Yield," or as a number that represents the position of the column in
the list: 1 (without quotation marks) for the first column, 2 for the second
column, and so on.
Criteria is the range of cells that contains the conditions you specify.
You can use any range for the criteria argument if it includes at least one
column label and at least one cell below the column label that specifies a
condition for the column.
Note The syntax of DVARP, DSTDEV, and DSTDEVP is the same as DVAR.
Example usage
The Excel Help files contain a useful example for these functions.
The following example is a slightly modified version of the example in the Help
files. The following example focuses on the computational aspects of the
functions.
Create a blank Excel worksheet, copy the following table,
select cell
A1 in your blank Excel worksheet, and then click
Paste on the
Edit menu so that the entries in
the table below fill cells A1:F15 in your worksheet.
Collapse this tableExpand this table
| Tree | Height | Age | Yield | Profit | Height |
| Apple | >10 | | | | <16 |
| Pear | | | | | |
| Tree | Height | Age | Yield | Profit | |
| Apple | 18 | 20 | =13 +
10^$F$12 | 105 | |
| Pear | 12 | 12 | =9 +
10^$F$12 | 96 | |
| Cherry | 13 | 14 | =8 +
10^$F$12 | 105 | |
| Apple | 14 | 15 | =9 +
10^$F$12 | 75 | |
| Pear | 9 | 8 | =7 +
10^$F$12 | 76.8 | |
| Apple | 8 | 9 | =5 +
10^$F$12 | 45 | |
| | | | | |
| =DSTDEV(A4:E10,"Yield",A1:A3) | The estimated standard
deviation in the yield of apple and pear trees if the data in the database is
only a sample of the total orchard population. (2.9664794) | =STDEV(D5,
D6, D8, D9, D10) | 16 | Power of 10 added to Yield | 9 |
| =DSTDEVP(A4:E10,"Yield",A1:A3) | The true standard
deviation in the yield of apple and pear trees if the data in the database is
the whole population. (2.6532998) | =STDEVP(D5, D6, D8, D9,
D10) | 12.8 | | |
| =DVAR(A4:E10,"Yield",A1:A3) | The estimated variance in
the yield of apple and pear trees if the data in the database is only a sample
of the total orchard population. (8.8000000) | =VAR(D5, D6, D8, D9,
D10) | 256 | | |
| =DVARP(A4:E10,"Yield",A1:A3) | The true variance in the
yield of apple and pear trees if the data in the database is the whole orchard
population. (
7.0400000) | =VARP(D5,D6,D8,D9,D10) | 163.84 | | |
You may want to format cells A12:A15 and C12:C15 as Number with
7 decimal places and cells D5:D10 as Number with 0 (zero) decimal
places.
This worksheet is designed to show the differences in behavior
between Excel 2002 (and earlier) and Excel 2003. Similar worksheets in the
articles for VAR, VARP, STDEV, and STDEVP give you an opportunity for a more
complete investigation of these differences.
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
826112�
Excel statistical functions: VAR
826393�
Excel statistical functions: VARP
826349�
Description of the STDEV function in Excel 2003
826406�
Excel statistical functions: STDEVP