DSTDEVP function

PRO BASIC

The DSTDEVP function in Jspreadsheet Formulas Pro is a useful tool that calculates the standard deviation of a population. It's used to measure the amount of variation or dispersion in a set of values, which can be very helpful in statistical analysis. By using this function, you can get a clearer understanding of how much your data varies from the average or mean. It's a straightforward and efficient way to analyze a large amount of data in Jspreadsheet.

Documentation

The DSTDEVP function calculates the standard deviation of a population.

Syntax

DSTDEVP(database, field, criteria)

Parameter	Description
`database`	The range or array containing the dataset representing the population.
`field`	The specific column or array within the database from which to calculate the standard deviation.
`criteria`	The conditions or criteria determining which data points are included in the calculation.

Behavior

The DSTDEVPSTDEVP function estimates the standard deviation based on an entire population. This function ignores empty cells, logical values, text, or error values in the database, or references that contain names, arrays, or references that contain logical values, text, or error values. However, cells with the value zero are included.

Common Errors

Error	Description
#VALUE!	This error occurs when the field argument does not match any column header in the first row, or if the criteria range is invalid.
#REF!	This error occurs if the given reference is not valid. It could be a result of deleting cells that are being referred to by the function.
#N/A	This error is displayed when the function doesn't find any rows that meet the criteria.
#DIV/0!	This error is displayed if there is only one row in the database that meets the criteria, as standard deviation requires at least two data points.

Best practices

Make sure that the criteria and database range are correctly defined to avoid errors such as #VALUE! or #N/A.

Avoid deleting cells that are being referred to by the function to prevent #REF! errors.

Ensure that your database contains at least two rows that meet the criteria to avoid #DIV/0! errors.

It is recommended to check your data for any non-numeric values, as DSTDEVP only considers numeric values. Text, logical values, and error values in the field are ignored.

Usage

A few examples using the DSTDEVP function.

DSTDEVP(A2:A10, B2:B10, C2:C10) returns the standard deviation of the population based on the specified database, field, and criteria.

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ODAxMzM2ZmEzNGQ3NzgyNjBjNDBkYWIyMDk0MjVmZDE1MjNhZjBlMDc1Mzk2NTBkZDdlYzY4MDMzZWQ0MjZlM2M0NWE1Yzk3ZWFkMjhmMTExZmViZGVhYzRhZTlmZDEzMGEwODYzZWM4NGQ3OTJjOTBiOGI0MWRjMWJkZjQxOTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product",
        "Sales",
        "Region"
    ],
    [
        "Laptop",
        1500,
        "North"
    ],
    [
        "Phone",
        800,
        "South"
    ],
    [
        "Tablet",
        1200,
        "North"
    ],
    [
        "Monitor",
        600,
        "South"
    ],
    [
        "Laptop",
        1800,
        "North"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Field",
        "Criteria",
        ""
    ],
    [
        "Sales",
        "North",
        ""
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Population StdDev:",
        "=DSTDEVP(A1:C6,\"Sales\",A8:B9)",
        ""
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('ODAxMzM2ZmEzNGQ3NzgyNjBjNDBkYWIyMDk0MjVmZDE1MjNhZjBlMDc1Mzk2NTBkZDdlYzY4MDMzZWQ0MjZlM2M0NWE1Yzk3ZWFkMjhmMTExZmViZGVhYzRhZTlmZDEzMGEwODYzZWM4NGQ3OTJjOTBiOGI0MWRjMWJkZjQxOTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Product",
        "Sales",
        "Region"
    ],
    [
        "Laptop",
        1500,
        "North"
    ],
    [
        "Phone",
        800,
        "South"
    ],
    [
        "Tablet",
        1200,
        "North"
    ],
    [
        "Monitor",
        600,
        "South"
    ],
    [
        "Laptop",
        1800,
        "North"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Field",
        "Criteria",
        ""
    ],
    [
        "Sales",
        "North",
        ""
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Population StdDev:",
        "=DSTDEVP(A1:C6,\"Sales\",A8:B9)",
        ""
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('ODAxMzM2ZmEzNGQ3NzgyNjBjNDBkYWIyMDk0MjVmZDE1MjNhZjBlMDc1Mzk2NTBkZDdlYzY4MDMzZWQ0MjZlM2M0NWE1Yzk3ZWFkMjhmMTExZmViZGVhYzRhZTlmZDEzMGEwODYzZWM4NGQ3OTJjOTBiOGI0MWRjMWJkZjQxOTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product",
        "Sales",
        "Region"
    ],
    [
        "Laptop",
        1500,
        "North"
    ],
    [
        "Phone",
        800,
        "South"
    ],
    [
        "Tablet",
        1200,
        "North"
    ],
    [
        "Monitor",
        600,
        "South"
    ],
    [
        "Laptop",
        1800,
        "North"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Field",
        "Criteria",
        ""
    ],
    [
        "Sales",
        "North",
        ""
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Population StdDev:",
        "=DSTDEVP(A1:C6,\"Sales\",A8:B9)",
        ""
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ODAxMzM2ZmEzNGQ3NzgyNjBjNDBkYWIyMDk0MjVmZDE1MjNhZjBlMDc1Mzk2NTBkZDdlYzY4MDMzZWQ0MjZlM2M0NWE1Yzk3ZWFkMjhmMTExZmViZGVhYzRhZTlmZDEzMGEwODYzZWM4NGQ3OTJjOTBiOGI0MWRjMWJkZjQxOTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Product",
        "Sales",
        "Region"
    ],
    [
        "Laptop",
        1500,
        "North"
    ],
    [
        "Phone",
        800,
        "South"
    ],
    [
        "Tablet",
        1200,
        "North"
    ],
    [
        "Monitor",
        600,
        "South"
    ],
    [
        "Laptop",
        1800,
        "North"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Field",
        "Criteria",
        ""
    ],
    [
        "Sales",
        "North",
        ""
    ],
    [
        "",
        "",
        ""
    ],
    [
        "Population StdDev:",
        "=DSTDEVP(A1:C6,\"Sales\",A8:B9)",
        ""
    ]
]
            }]
        });
    }
}

Edit this page on GitHub