DSTDEVP |
DSTDEVP(database, field, criteria) |
Returns the standard deviation of the values in a column that satisfies multiple conditions based on an entire population. |
database | The range of cells that make up the table or list. |
field | The column name or number indicating which column to use. |
criteria | The range of cells that contain the conditions. |
REMARKS |
* For an illustrated example refer to the Specifying Database Criteria page. * The standard deviation is a measure of how [[Dispersed]] the values are from the mean. * Any cells containing logical values, text and errors are not included. See Example 7. * This function supports wildcards (? and *). * The "database" range of cells can be a single column. See Example 5. * The "database" range of cells must contain column headings in the first row. * If the "database" range refers to a cell inside a pivot table, this will refer to the data currently displayed in the pivot table. * The "field" can be a column label, a column number (left to right) or a cell reference. * The "field" label is not case sensitive. See Example 2. * If the "field" is left blank, then #VALUE! is returned. * If the "field" column contains some text entries, then these are ignored. * The "criteria" must include at least one column heading and one cell below the column heading. * The "criteria" must be in a contiguous block of adjacent cells. * The "criteria" can be anywhere on the active sheet, although it is recommended not to put it below the data, in case more data is added later on. * If the "criteria" is left blank, then #VALUE! is returned. See Example 18. * Make sure the "criteria" and "database" ranges do not overlap and that they are always separated by at least one blank row or column. * To perform an operation on an entire column in a database, enter a blank cell below the column heading in the "criteria" range. See Example 10. * If no rows match the criteria, then 0 is returned. See Example 15. * You can use the DSTDEV function to return the standard deviation of the values that satisfies multiple conditions based on a sample. * You can use the STDEV.P function to return the standard deviation based on an entire population. * You can use the STDEVPA function to return the standard deviation based on an entire population (including logical values and text). * This is similar to DAVERAGE, DCOUNT, DGET, DMAX, DMIN, DPRODUCT, DSUM and DVAR functions. * For the Microsoft documentation refer to support.microsoft.com * For the Google documentation refer to support.google.com |
|
1 - What is the standard deviation of the "Qty" when Date="Jan", using a field label. (19) (one value) 2 - What is the standard deviation of the "QTY" when Date="Jan", with the field label in uppercase. (19) (one value) 3 - What is the standard deviation of the "Qty" when Date="Jan", using a field number. (19) (one value) 4 - What is the standard deviation of the "Cost" when Date="Jan" or "Jun". (18000, 20000) 5 - What is the standard deviation of the "Qty" when Qty<22. This uses a single column. (19, 20,17,18,21,17) 6 - What is the standard deviation of the "Qty" when Qty<22 OR Qty>18. (all the numbers) 7 - What is the standard deviation of the "Cost" when Qty<22 AND Cost>20,000. The Cost for May is not included. (24000, 24000) (same value) 8 - What is the standard deviation of the "Qty" when Cost>20,000. (20, 21, 24) 9 - What is the standard deviation of the "Cost" when Date="Jan", Qty<22 and Cost>20,000 OR when Date="Jun" and Qty>18. (20000) (one value) 10 - What is the standard deviation of the "Cost" for all the items. Putting a blank cell below the column heading will refer to the entire column. (all the numbers) 11 - What is the standard deviation of the "Cost" when Qty>18 and Cost has no criteria. (18000, 24000, 20000, 24000, 29000) 12 - What is the standard deviation of the "Cost" for all the dates that start with the letter "J". (18000, 20000) 13 - If the "field" column does not contain any numbers, then #DIV/0! is returned. 14 - If the "criteria" you specify does not return any rows, then #DIV/0! is returned. The Cost for January was only 18,000. 15 - If the "field" does not match one of the column headings, then #VALUE! is returned. 16 - If the "criteria" only includes a column heading, then #VALUE! is returned. 17 - If the "criteria" refers to a single empty cell, then #VALUE! is returned. C19 is empty. 18 - If the "criteria" is left blank, then #VALUE! is returned. 19 - If the "database" range does not include column headings, then #VALUE! is returned. 20 - If the "database" range only includes column headings, then #VALUE! is returned. |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top