Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Excel statistical functions: DVAR, DVARP, DSTDEV, and DSTDEVP


View products that this article applies to.

Summary

The computational formulas for the DVAR, DVARP, DSTDEV, and DSTDEVP functions have been improved for Excel 2003. This article discusses the effects of these improvements.

Microsoft Excel 2004 for Mac information

The statistical functions in Excel 2004 for Mac were updated by using the same algorithms that were used to update the statistical functions in Microsoft Office Excel 2003. Any information in this article that describes how a function works or how a function was modified for Excel 2003 also applies to Excel 2004 for Mac.

↑ Back to the top


More information

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)
Database 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
TreeHeightAgeYieldProfitHeight
Apple>10<16
Pear
TreeHeightAgeYieldProfit
Apple1820=13 + 10^$F$12105
Pear1212=9 + 10^$F$1296
Cherry1314=8 + 10^$F$12105
Apple1415=9 + 10^$F$1275
Pear98=7 + 10^$F$1276.8
Apple89=5 + 10^$F$1245
=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)16Power of 10 added to Yield9
=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

↑ Back to the top


Keywords: KB828125, kbinfo

↑ Back to the top

Article Info
Article ID : 828125
Revision : 6
Created on : 1/11/2006
Published on : 1/11/2006
Exists online : False
Views : 295