CRITBINOM function

PRO

The CRITBINOM function in Jspreadsheet Formulas Pro is used for calculating the minimum value at which the cumulative binomial distribution is equal to or lesser than a set criterion value. In simple terms, it helps in finding the lowest number of successful trials required to meet a certain probability in a series of independent experiments. This function is extremely useful in statistical analysis and probability distribution studies, helping users analyze and predict outcomes.

Documentation

Calculates the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.

Syntax

CRITBINOM(trials, probability_s, alpha)

Parameter	Description
`trials`	The number of Bernoulli trials.
`probability_s`	The probability of success on each trial.
`alpha`	The cumulative probability threshold (i.e. significance level) to compare against.

Behavior

The CRITBINOM function is used to calculate the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value. This function requires three arguments: the number of trials, the probability of success on each trial, and the criterion value and returns the smallest integer (number of successes) such that the cumulative binomial probability is greater than or equal to alpha.

If an argument cell is empty, it is treated as 0, which can lead to #NUM! errors depending on the parameter.
If text is used as a value in the function, it will return a #VALUE! error.
Booleans are considered as 1 (TRUE) and 0 (FALSE).
If any of the arguments is non-numeric, the function returns a #VALUE! error.
If the number of trials is less than zero, the function will return a #NUM! error.
If the probability of success is less than zero or greater than one, the function returns a #NUM! error.
If the criterion value is less than zero, the function returns a #NUM! error.

Common Errors

Error	Description
`#VALUE!`	This error is returned when one or more of the arguments are non-numeric or if text is used as an argument.
`#NUM!`	This error is returned when the number of trials, the probability of success, or the criterion value are less than zero, or if the probability of success is greater than one.

Best practices

Always ensure that the arguments provided are numeric. Non-numeric values will result in a #VALUE! error.

Ensure trials is a non-negative integer, and alpha is between 0 and 1 inclusive. Negative values will result in a #NUM! error.

Check that the probability of success is within the range of 0 and 1. Values outside this range will return a #NUM! error.

Use cell references instead of direct number input for arguments in the CRITBINOM function. This makes the function more flexible and allows the spreadsheet to update automatically when the referenced cell values change.

Usage

A few examples using the CRITBINOM function.

CRITBINOM(10, 0.2, 0.05)      // Returns: 0
CRITBINOM(20, 0.5, 0.01)      // Returns: 5
CRITBINOM(50, 0.3, 0.1)       // Returns: 11
CRITBINOM(-5, 0.5, 0.05)      // Returns: #NUM!
CRITBINOM(10, 1.5, 0.05)      // Returns: #NUM!
CRITBINOM(10, 0.5, "alpha")   // Returns: #VALUE!

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Trials",
        "Probability",
        "Alpha",
        "Critical Value"
    ],
    [
        10,
        0.2,
        0.05,
        "=CRITBINOM(A2,B2,C2)"
    ],
    [
        20,
        0.5,
        0.01,
        "=CRITBINOM(A3,B3,C3)"
    ],
    [
        50,
        0.3,
        0.1,
        "=CRITBINOM(A4,B4,C4)"
    ],
    [
        100,
        0.15,
        0.25,
        "=CRITBINOM(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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Trials",
        "Probability",
        "Alpha",
        "Critical Value"
    ],
    [
        10,
        0.2,
        0.05,
        "=CRITBINOM(A2,B2,C2)"
    ],
    [
        20,
        0.5,
        0.01,
        "=CRITBINOM(A3,B3,C3)"
    ],
    [
        50,
        0.3,
        0.1,
        "=CRITBINOM(A4,B4,C4)"
    ],
    [
        100,
        0.15,
        0.25,
        "=CRITBINOM(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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Trials",
        "Probability",
        "Alpha",
        "Critical Value"
    ],
    [
        10,
        0.2,
        0.05,
        "=CRITBINOM(A2,B2,C2)"
    ],
    [
        20,
        0.5,
        0.01,
        "=CRITBINOM(A3,B3,C3)"
    ],
    [
        50,
        0.3,
        0.1,
        "=CRITBINOM(A4,B4,C4)"
    ],
    [
        100,
        0.15,
        0.25,
        "=CRITBINOM(A5,B5,C5)"
    ]
]

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

// 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: [
    [
        "Trials",
        "Probability",
        "Alpha",
        "Critical Value"
    ],
    [
        10,
        0.2,
        0.05,
        "=CRITBINOM(A2,B2,C2)"
    ],
    [
        20,
        0.5,
        0.01,
        "=CRITBINOM(A3,B3,C3)"
    ],
    [
        50,
        0.3,
        0.1,
        "=CRITBINOM(A4,B4,C4)"
    ],
    [
        100,
        0.15,
        0.25,
        "=CRITBINOM(A5,B5,C5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub