DocumentationFormulasFunctionsBeta inv

BETA.INV function

PRO

The BETA.INV function in Jspreadsheet Formulas Pro returns the inverse of the cumulative beta distribution: given a probability, it finds the value x for which BETA.DIST(x, alpha, beta, TRUE) equals that probability. It is used to compute quantiles of proportions, such as the completion rate that a project has a 90% chance of staying below. It returns the same results as the compatibility function BETAINV.

Documentation

Returns the inverse of the cumulative beta probability density function.

Category

Statistical

Syntax

BETA.INV(probability, alpha, beta, [A], [B])

Parameter Description
probability The cumulative probability, strictly between 0 and 1.
alpha The first shape parameter of the distribution. Must be greater than 0.
beta The second shape parameter of the distribution. Must be greater than 0.
[A] Optional. The lower bound of the interval of x. Defaults to 0.
[B] Optional. The upper bound of the interval of x. Defaults to 1.

Behavior

The BETA.INV function returns a value between A and B such that the cumulative probability up to that value equals probability.

  • probability must be strictly between 0 and 1. Both 0 and 1 return #NUM!.
  • alpha and beta must be greater than 0.
  • The result is scaled to the interval [A, B] when the bounds are provided.
  • It is the inverse of BETADIST and of BETA.DIST with cumulative TRUE, so BETA.INV(BETADIST(x, a, b), a, b) returns x.
  • 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 alpha or beta 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.
  • Reference alpha and beta from cells validated to be greater than 0.
  • Use absolute references for the alpha and beta 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 BETA.INV function.

BETA.INV(0.488, 2, 3)
// Returns 0.378872

BETA.INV(0.9, 4, 2, 0.5, 1)
// Returns 0.943883, using the interval from 0.5 to 1

BETA.INV(0.1808, 2, 3)
// Returns 0.2, the inverse of BETADIST(0.2, 2, 3)

BETA.INV(1.5, 2, 3)
// 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('ZjE4ZDdmYTYzYzllODlmOGY0MjQ0YTA5MjMxMjBjMTVjY2ZiODY3YzQ4NWE5Mjg3M2NiZWZhMjQ1MGYzZTAzODEyMTA0NWZjMmQ3NmQ4OWEzZjM0ZTUzZTIwYTA0ZDdkZTcyN2FhMmQwMDVjNjM3YzhkNTEwMmNjZGIzNDU0MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNamczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "probability",
        "alpha",
        "beta",
        "BETA.INV"
    ],
    [
        0.1,
        2,
        3,
        "=BETA.INV(A2, B2, C2)"
    ],
    [
        0.488,
        2,
        3,
        "=BETA.INV(A3, B3, C3)"
    ],
    [
        0.9,
        2,
        3,
        "=BETA.INV(A4, B4, C4)"
    ],
    [
        0.5,
        1,
        1,
        "=BETA.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('ZjE4ZDdmYTYzYzllODlmOGY0MjQ0YTA5MjMxMjBjMTVjY2ZiODY3YzQ4NWE5Mjg3M2NiZWZhMjQ1MGYzZTAzODEyMTA0NWZjMmQ3NmQ4OWEzZjM0ZTUzZTIwYTA0ZDdkZTcyN2FhMmQwMDVjNjM3YzhkNTEwMmNjZGIzNDU0MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNamczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "probability",
        "alpha",
        "beta",
        "BETA.INV"
    ],
    [
        0.1,
        2,
        3,
        "=BETA.INV(A2, B2, C2)"
    ],
    [
        0.488,
        2,
        3,
        "=BETA.INV(A3, B3, C3)"
    ],
    [
        0.9,
        2,
        3,
        "=BETA.INV(A4, B4, C4)"
    ],
    [
        0.5,
        1,
        1,
        "=BETA.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('ZjE4ZDdmYTYzYzllODlmOGY0MjQ0YTA5MjMxMjBjMTVjY2ZiODY3YzQ4NWE5Mjg3M2NiZWZhMjQ1MGYzZTAzODEyMTA0NWZjMmQ3NmQ4OWEzZjM0ZTUzZTIwYTA0ZDdkZTcyN2FhMmQwMDVjNjM3YzhkNTEwMmNjZGIzNDU0MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNamczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "probability",
        "alpha",
        "beta",
        "BETA.INV"
    ],
    [
        0.1,
        2,
        3,
        "=BETA.INV(A2, B2, C2)"
    ],
    [
        0.488,
        2,
        3,
        "=BETA.INV(A3, B3, C3)"
    ],
    [
        0.9,
        2,
        3,
        "=BETA.INV(A4, B4, C4)"
    ],
    [
        0.5,
        1,
        1,
        "=BETA.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('ZjE4ZDdmYTYzYzllODlmOGY0MjQ0YTA5MjMxMjBjMTVjY2ZiODY3YzQ4NWE5Mjg3M2NiZWZhMjQ1MGYzZTAzODEyMTA0NWZjMmQ3NmQ4OWEzZjM0ZTUzZTIwYTA0ZDdkZTcyN2FhMmQwMDVjNjM3YzhkNTEwMmNjZGIzNDU0MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNamczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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",
        "alpha",
        "beta",
        "BETA.INV"
    ],
    [
        0.1,
        2,
        3,
        "=BETA.INV(A2, B2, C2)"
    ],
    [
        0.488,
        2,
        3,
        "=BETA.INV(A3, B3, C3)"
    ],
    [
        0.9,
        2,
        3,
        "=BETA.INV(A4, B4, C4)"
    ],
    [
        0.5,
        1,
        1,
        "=BETA.INV(A5, B5, C5)"
    ]
]
            }]
        });
    }
}