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.
Category
Statistical
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
orISNUMBER
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('ZmNmMjExMDVlNzg3YjcyMjBiMTEzMzNjMDE2ZTQwN2FmNjhlY2YxNDRhNjEzNDI1NzJiNDVmMmZkNGJjZDU5NDBiZjg1OTc0M2ViNGM3N2IxYmYyZjI4NThiNjhkMDBkN2FlMmE0YjUwOGZkMjEyN2U2MjA2ZGIyMjMwNDVkOWQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('ZmNmMjExMDVlNzg3YjcyMjBiMTEzMzNjMDE2ZTQwN2FmNjhlY2YxNDRhNjEzNDI1NzJiNDVmMmZkNGJjZDU5NDBiZjg1OTc0M2ViNGM3N2IxYmYyZjI4NThiNjhkMDBkN2FlMmE0YjUwOGZkMjEyN2U2MjA2ZGIyMjMwNDVkOWQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('ZmNmMjExMDVlNzg3YjcyMjBiMTEzMzNjMDE2ZTQwN2FmNjhlY2YxNDRhNjEzNDI1NzJiNDVmMmZkNGJjZDU5NDBiZjg1OTc0M2ViNGM3N2IxYmYyZjI4NThiNjhkMDBkN2FlMmE0YjUwOGZkMjEyN2U2MjA2ZGIyMjMwNDVkOWQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('ZmNmMjExMDVlNzg3YjcyMjBiMTEzMzNjMDE2ZTQwN2FmNjhlY2YxNDRhNjEzNDI1NzJiNDVmMmZkNGJjZDU5NDBiZjg1OTc0M2ViNGM3N2IxYmYyZjI4NThiNjhkMDBkN2FlMmE0YjUwOGZkMjEyN2U2MjA2ZGIyMjMwNDVkOWQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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"
]
]
}]
});
}
}