GAMMA.INV function

PRO

The GAMMA.INV function in Jspreadsheet Formulas Pro returns the inverse of the gamma cumulative distribution: given a probability, it finds the value x for which GAMMA.DIST(x, alpha, beta, TRUE) equals that probability. It is used to compute percentiles of right-skewed quantities, for example the waiting time that is exceeded only 10% of the time. It returns the same values as the compatibility function GAMMAINV.

Documentation

Returns the inverse of the gamma cumulative distribution for a specified probability, shape and scale.

Syntax

GAMMA.INV(probability, alpha, beta)

Parameter	Description
`probability`	The cumulative probability, between 0 and 1.
`alpha`	The shape parameter. Must be greater than 0.
`beta`	The scale parameter. Must be greater than 0.

Behavior

The GAMMA.INV function returns the value x such that GAMMA.DIST(x, alpha, beta, TRUE) equals probability.

probability 0 returns 0. Values above 1, and exactly 1, return #NUM!.
alpha and beta must be positive. Otherwise the function returns #NUM!.
The result grows with probability, so a 0.9 percentile is always larger than a 0.5 percentile for the same parameters.
Text arguments return #VALUE!.

Common Errors

Error	Description
#NUM!	Returned when `probability` is negative 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 below 1. Exactly 1 and larger values return #NUM!, so wrap the call with IFERROR when the probability comes from data.

Validate the input cells so that alpha and beta are positive.

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 GAMMA.INV function.

GAMMA.INV(0.3, 3, 2)
// Returns 3.827552

GAMMA.INV(0.7, 5, 1.5)
// Returns 8.835542

GAMMA.INV(0.9, 7, 4)
// Returns 42.128288

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

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

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

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

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

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

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

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

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