Z.TEST function

PRO BASIC

The Z.TEST function in Jspreadsheet Formulas Pro function in Jspreadsheet calculates the one-tailed probability value (p-value) of a z-test, which tests whether the mean of a sample differs significantly from a hypothesized population mean. It is commonly used in hypothesis testing.

Documentation

Returns the probability of a one-tailed, two-sample z-test.

Syntax

Z.TEST(array, x, [sigma])

Parameter	Description
`array1`	The first set of data.
`x`	The value to test against (hypothesized population mean).
`sigma`	Optional. The population (known) standard deviation. If omitted, the sample standard deviation is used instead.

Behavior

The Z.TEST function calculates the one-tailed P-value of a z-test. This function requires an array or range of data points, a value to test against, and optionally, a standard deviation. Here's how it handles different scenarios:

Empty Cells: If there are empty cells in the range, Z.TEST will ignore them.
Text: If the range includes cells with text, Z.TEST will return a #VALUE! error.
Booleans: Boolean values are treated as numbers (TRUE = 1, FALSE = 0), which may produce unexpected results.
Errors: If the range includes cells with errors, Z.TEST will return the same error.
Specific Value: If the specific value to test against is not provided, Z.TEST will return a #N/A error.
Standard Deviation: If the standard deviation is not provided, Z.TEST will calculate it from the given data.

Common Errors

Error	Description
#VALUE!	The function returns this error when the given range includes cells with non-numeric values like text or boolean.
#N/A	The function returns this error when the specific value to test against is not provided.
#DIV/0!	The function returns this error when the standard deviation of the data set is zero.

Best practices

Always ensure that the data range only contains numeric values to avoid #VALUE! error.

Always provide the value to test against to avoid #N/A error.

Be cautious when excluding the standard deviation. Without it, Z.TEST will compute the standard deviation from the given data, which might not always produce desired results.

Use Z.TEST to find the one-tailed p-value. For a two-tailed test, multiply the smaller of the one-tailed result and its complement by 2.

Usage

A few examples using the Z.TEST function.

// One-tailed z-test comparing sample mean to hypothesized mean = 4
Z.TEST(A1:A5, 4)  

// One-tailed z-test with known population standard deviation = 1
Z.TEST(A1:A5, 4, 1)

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('M2M5N2RhZmYxMjMwY2Y4NDliNTBjNTk3YWRlYTJiMTJhN2FiYWYzODRkM2ZiOGYwOTU4N2NhNDBkZGM1NzlkZTI0ZGYxMTM4ODdhYjY2YWFhYjhhNjhiODAyZDQzMWNlYjAyZmVkZmQ4ZjM3MTA3NzQ0NTMzZmYwYjEzZmU0MGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOVEU0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sample 1",
        "Sample 2",
        "Z-Test Result"
    ],
    [
        85,
        78,
        "=Z.TEST(A2:A6,B2:B6,5)"
    ],
    [
        92,
        82
    ],
    [
        88,
        85
    ],
    [
        90,
        79
    ],
    [
        87,
        83
    ]
]
  }]
});
</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('M2M5N2RhZmYxMjMwY2Y4NDliNTBjNTk3YWRlYTJiMTJhN2FiYWYzODRkM2ZiOGYwOTU4N2NhNDBkZGM1NzlkZTI0ZGYxMTM4ODdhYjY2YWFhYjhhNjhiODAyZDQzMWNlYjAyZmVkZmQ4ZjM3MTA3NzQ0NTMzZmYwYjEzZmU0MGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOVEU0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Sample 1",
        "Sample 2",
        "Z-Test Result"
    ],
    [
        85,
        78,
        "=Z.TEST(A2:A6,B2:B6,5)"
    ],
    [
        92,
        82
    ],
    [
        88,
        85
    ],
    [
        90,
        79
    ],
    [
        87,
        83
    ]
];

    // 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('M2M5N2RhZmYxMjMwY2Y4NDliNTBjNTk3YWRlYTJiMTJhN2FiYWYzODRkM2ZiOGYwOTU4N2NhNDBkZGM1NzlkZTI0ZGYxMTM4ODdhYjY2YWFhYjhhNjhiODAyZDQzMWNlYjAyZmVkZmQ4ZjM3MTA3NzQ0NTMzZmYwYjEzZmU0MGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOVEU0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sample 1",
        "Sample 2",
        "Z-Test Result"
    ],
    [
        85,
        78,
        "=Z.TEST(A2:A6,B2:B6,5)"
    ],
    [
        92,
        82
    ],
    [
        88,
        85
    ],
    [
        90,
        79
    ],
    [
        87,
        83
    ]
]

        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('M2M5N2RhZmYxMjMwY2Y4NDliNTBjNTk3YWRlYTJiMTJhN2FiYWYzODRkM2ZiOGYwOTU4N2NhNDBkZGM1NzlkZTI0ZGYxMTM4ODdhYjY2YWFhYjhhNjhiODAyZDQzMWNlYjAyZmVkZmQ4ZjM3MTA3NzQ0NTMzZmYwYjEzZmU0MGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOVEU0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "Sample 1",
        "Sample 2",
        "Z-Test Result"
    ],
    [
        85,
        78,
        "=Z.TEST(A2:A6,B2:B6,5)"
    ],
    [
        92,
        82
    ],
    [
        88,
        85
    ],
    [
        90,
        79
    ],
    [
        87,
        83
    ]
]
            }]
        });
    }
}

Edit this page on GitHub