NORMSINV function

PRO

The NORMSINV function in Jspreadsheet Formulas Pro is a powerful tool that gives you the inverse of the standard normal cumulative distribution. To put it simply, it translates a probability into a score on a normally distributed curve, often used in statistical analysis. This function is useful in scenarios like forecasting and predicting data trends, by converting percentages back into raw scores. It gives you the z-score associated with a particular cumulative probability in a standard normal distribution.

Documentation

Returns the inverse of the standard normal cumulative distribution.

Syntax

NORMSINV(probability)

Parameter	Description
`probability`	The probability associated with a normal distribution.

Behavior

The NORMSINV function calculates the inverse of the standard normal cumulative distribution function. This function takes a single argument, which should be a probability value between 0 and 1.

If the cell is empty, NORMSINV will return a #NUM! error since it expects a numeric value between 0 and 1.
If the cell contains text, NORMSINV will return a #VALUE! error as it cannot process text.
If the cell contains a boolean, it will be converted to a binary number (TRUE as 1 and FALSE as 0) before calculating.
If the cell contains a probability value less than 0 or greater than 1, NORMSINV will return a #NUM! error.
If the cell contains an error, the function will propagate the error.

Common Errors

Error	Description
`#NUM!`	If no numeric argument is provided (e.g., the cell is empty), the function returns a #NUM! error.
`#VALUE!`	This error occurs when the provided argument is non-numeric, such as text.
Error propagation	If the argument of the `NORMSINV` function is another function that returns an error, `NORMSINV` will also return an error.

Best practices

Always ensure that the argument given to NORMSINV is a probability value between 0 and 1.

Avoid using text or boolean values as the argument to NORMSINV. If necessary, convert these values to numbers before using the function.

Be cautious of error propagation when using NORMSINV in combination with other functions.

Use error handling functions like IFERROR or ISNUMBER to handle potential errors and maintain the cleanliness of your data.

Usage

A few examples using the NORMSINV function.

NORMSINV(0.025) → -1.96   (z-score at the 2.5% lower tail, used in 95% confidence intervals)  
NORMSINV(0.5) → 0         (median of the distribution)  
NORMSINV(0.975) → 1.96    (z-score at the 97.5% upper tail, used in 95% confidence intervals)  
NORMSINV(0.99865) → 3     (z-score at the 99.87% level, used in Six Sigma analysis)

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Probability",
        "Z-Score",
        "Description"
    ],
    [
        0.025,
        "=NORMSINV(A2)",
        "Lower 2.5% tail"
    ],
    [
        0.5,
        "=NORMSINV(A3)",
        "50th percentile (median)"
    ],
    [
        0.975,
        "=NORMSINV(A4)",
        "Upper 2.5% tail"
    ],
    [
        0.95,
        "=NORMSINV(A5)",
        "95th percentile"
    ]
]
  }]
});
</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('NmQyNmQxZjk4OTQyZDNmZGZkODExNjFlMDQ3ZTQ0N2Q3YjUyYmMyYjM1ZjBmZDc4MTA5YzJiYTNjNGI4OWY0YmE2YTgyMzhlZGQwYTI3NDA5MjQxMTJmYjRlMmUxMjQxNmUyYTYwN2I1MGVlODdjN2JhYjQyYWQ0NDcxYmU5ZmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Probability",
        "Z-Score",
        "Description"
    ],
    [
        0.025,
        "=NORMSINV(A2)",
        "Lower 2.5% tail"
    ],
    [
        0.5,
        "=NORMSINV(A3)",
        "50th percentile (median)"
    ],
    [
        0.975,
        "=NORMSINV(A4)",
        "Upper 2.5% tail"
    ],
    [
        0.95,
        "=NORMSINV(A5)",
        "95th percentile"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Probability",
        "Z-Score",
        "Description"
    ],
    [
        0.025,
        "=NORMSINV(A2)",
        "Lower 2.5% tail"
    ],
    [
        0.5,
        "=NORMSINV(A3)",
        "50th percentile (median)"
    ],
    [
        0.975,
        "=NORMSINV(A4)",
        "Upper 2.5% tail"
    ],
    [
        0.95,
        "=NORMSINV(A5)",
        "95th percentile"
    ]
]

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

// 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: [
    [
        "Probability",
        "Z-Score",
        "Description"
    ],
    [
        0.025,
        "=NORMSINV(A2)",
        "Lower 2.5% tail"
    ],
    [
        0.5,
        "=NORMSINV(A3)",
        "50th percentile (median)"
    ],
    [
        0.975,
        "=NORMSINV(A4)",
        "Upper 2.5% tail"
    ],
    [
        0.95,
        "=NORMSINV(A5)",
        "95th percentile"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub