NORM.INV function

PRO

The NORM.INV function in Jspreadsheet Formulas Pro returns the inverse of the normal cumulative distribution: given a probability, it finds the value x for which NORM.DIST(x, mean, standard_dev, TRUE) equals that probability. It is used to compute percentiles and interval limits. It returns the same values as the compatibility function NORMINV.

Documentation

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Syntax

NORM.INV(probability, mean, standard_dev)

Parameter	Description
`probability`	The cumulative probability, strictly between 0 and 1.
`mean`	The arithmetic mean of the distribution.
`standard_dev`	The standard deviation of the distribution. Must be greater than 0.

Behavior

The NORM.INV function returns mean + standard_dev * NORM.S.INV(probability).

probability must be strictly between 0 and 1. Both 0 and 1 return #NUM!.
standard_dev must be positive. A value of 0 returns #NUM!.
A probability of 0.5 returns the mean.
Text arguments return #VALUE!.

Common Errors

Error	Description
#NUM!	Returned when `probability` is less than or equal to 0 or greater than or equal to 1, or when `standard_dev` is less than or equal to 0.
#VALUE!	Returned when an argument is text that cannot be interpreted as a number.

Best practices

Keep probability strictly between 0 and 1. Exactly 0 or 1 returns #NUM!, so clamp inputs from data with MIN and MAX or handle them with IFERROR.

Validate the input cells so that standard_dev is positive.

Use absolute references for the mean and standard_dev cells when copying the formula down a column of probability values, for example $B$1.

Check with ISNUMBER that the input cells hold numbers, because text returns #VALUE!.

Usage

A few examples using the NORM.INV function.

NORM.INV(0.75, 80, 6)
// Returns 84.046939

NORM.INV(0.95, 0, 1)
// Returns 1.644854

NORM.INV(0.5, 80, 6)
// Returns 80, the mean

NORM.INV(1.5, 0, 1)
// Returns #NUM! because the probability is above 1

Interactive Spreadsheet Demo

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.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>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "probability",
        "mean",
        "standard_dev",
        "NORM.INV"
    ],
    [
        0.025,
        80,
        6,
        "=NORM.INV(A2, B2, C2)"
    ],
    [
        0.5,
        80,
        6,
        "=NORM.INV(A3, B3, C3)"
    ],
    [
        0.75,
        80,
        6,
        "=NORM.INV(A4, B4, C4)"
    ],
    [
        0.975,
        80,
        6,
        "=NORM.INV(A5, B5, C5)"
    ]
]
  }]
});
</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('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "probability",
        "mean",
        "standard_dev",
        "NORM.INV"
    ],
    [
        0.025,
        80,
        6,
        "=NORM.INV(A2, B2, C2)"
    ],
    [
        0.5,
        80,
        6,
        "=NORM.INV(A3, B3, C3)"
    ],
    [
        0.75,
        80,
        6,
        "=NORM.INV(A4, B4, C4)"
    ],
    [
        0.975,
        80,
        6,
        "=NORM.INV(A5, B5, C5)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "probability",
        "mean",
        "standard_dev",
        "NORM.INV"
    ],
    [
        0.025,
        80,
        6,
        "=NORM.INV(A2, B2, C2)"
    ],
    [
        0.5,
        80,
        6,
        "=NORM.INV(A3, B3, C3)"
    ],
    [
        0.75,
        80,
        6,
        "=NORM.INV(A4, B4, C4)"
    ],
    [
        0.975,
        80,
        6,
        "=NORM.INV(A5, B5, C5)"
    ]
]

        return {
            data
        };
    }
}
</script>

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

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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",
        "mean",
        "standard_dev",
        "NORM.INV"
    ],
    [
        0.025,
        80,
        6,
        "=NORM.INV(A2, B2, C2)"
    ],
    [
        0.5,
        80,
        6,
        "=NORM.INV(A3, B3, C3)"
    ],
    [
        0.75,
        80,
        6,
        "=NORM.INV(A4, B4, C4)"
    ],
    [
        0.975,
        80,
        6,
        "=NORM.INV(A5, B5, C5)"
    ]
]
            }]
        });
    }
}