DocumentationFormulasFunctionsBinomdist

BINOMDIST function

PRO

The BINOMDIST function in Jspreadsheet Formulas Pro returns the binomial distribution probability: the chance of observing a given number of successes in a fixed number of independent trials, each with the same probability of success. It is the compatibility version of BINOM.DIST and returns exactly the same values. Typical uses are quality control, such as the probability that at most two items in a batch of ten are defective, and any yes/no experiment repeated a fixed number of times.

Documentation

Returns the individual term binomial distribution probability.

Category

Statistical

Syntax

BINOMDIST(number_s, trials, probability_s, cumulative)

Parameter Description
number_s The number of successes in the trials.
trials The number of independent trials.
probability_s The probability of success on each trial, between 0 and 1.
cumulative TRUE returns the probability of at most number_s successes. FALSE returns the probability of exactly number_s successes.

Behavior

The BINOMDIST function evaluates the probability mass function when cumulative is FALSE and the cumulative distribution function when it is TRUE.

  • cumulative also accepts numbers: 0 is treated as FALSE and any other number as TRUE.
  • number_s must be between 0 and trials, and probability_s must be between 0 and 1. Otherwise the function returns #NUM!.
  • A non-integer number_s is not truncated to an integer as in Excel. BINOMDIST(3.7, 5, 0.5, FALSE) returns 0.208287, which differs from BINOMDIST(3, 5, 0.5, FALSE). Round the value first when you need Excel-compatible results.
  • Text arguments return #VALUE!.

Common Errors

Error Description
#NUM! Returned when number_s is negative or greater than trials, or when probability_s is outside the range 0 to 1.
#VALUE! Returned when an argument is text that cannot be interpreted as a number.

Best practices

  • Pass cumulative explicitly as TRUE or FALSE so the intent is clear when reading the formula.
  • Wrap number_s and trials with INT when they come from calculations, because decimal values are not truncated.
  • Keep number_s at most equal to trials and probability_s between 0 and 1, or wrap the call with IFERROR to handle #NUM!.
  • Use absolute references for the trials and probability_s cells when copying the formula down a column of number_s values, for example $B$1.

Usage

A few examples using the BINOMDIST function.

BINOMDIST(3, 5, 0.5, FALSE)
// Returns 0.3125, the probability of exactly 3 successes in 5 trials

BINOMDIST(2, 10, 0.1, TRUE)
// Returns 0.929809, the probability of at most 2 successes in 10 trials

BINOMDIST(3, 5, 0.5, 1)
// Returns 0.8125, a numeric cumulative flag is accepted

BINOMDIST(6, 5, 0.5, FALSE)
// Returns #NUM! because number_s is greater than trials

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "number_s",
        "trials",
        "probability_s",
        "cumulative",
        "BINOMDIST"
    ],
    [
        3,
        5,
        0.5,
        false,
        "=BINOMDIST(A2, B2, C2, D2)"
    ],
    [
        3,
        5,
        0.5,
        true,
        "=BINOMDIST(A3, B3, C3, D3)"
    ],
    [
        2,
        10,
        0.1,
        true,
        "=BINOMDIST(A4, B4, C4, D4)"
    ],
    [
        4,
        8,
        0.7,
        false,
        "=BINOMDIST(A5, B5, C5, D5)"
    ]
]
  }]
});
</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('N2IxM2NmZmQ2YzhlZjgwNWEwNDM5YzlmNWEzODllZGNkNzU3YmE1ZDE3NDcyMzc3ZTJkNWIzZDI1ZTFkNDM1NGU0ZTIwMjg2MGEwNGE2MWI3OGVhMGE4NGVkN2ViZTFkODUyM2RjOTRiMzI4ZWQ2OWFhOTM4YzdlMGM0Y2RlNWIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9EYzBPVEkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "number_s",
        "trials",
        "probability_s",
        "cumulative",
        "BINOMDIST"
    ],
    [
        3,
        5,
        0.5,
        false,
        "=BINOMDIST(A2, B2, C2, D2)"
    ],
    [
        3,
        5,
        0.5,
        true,
        "=BINOMDIST(A3, B3, C3, D3)"
    ],
    [
        2,
        10,
        0.1,
        true,
        "=BINOMDIST(A4, B4, C4, D4)"
    ],
    [
        4,
        8,
        0.7,
        false,
        "=BINOMDIST(A5, B5, C5, D5)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "number_s",
        "trials",
        "probability_s",
        "cumulative",
        "BINOMDIST"
    ],
    [
        3,
        5,
        0.5,
        false,
        "=BINOMDIST(A2, B2, C2, D2)"
    ],
    [
        3,
        5,
        0.5,
        true,
        "=BINOMDIST(A3, B3, C3, D3)"
    ],
    [
        2,
        10,
        0.1,
        true,
        "=BINOMDIST(A4, B4, C4, D4)"
    ],
    [
        4,
        8,
        0.7,
        false,
        "=BINOMDIST(A5, B5, C5, D5)"
    ]
]

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

// 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: [
    [
        "number_s",
        "trials",
        "probability_s",
        "cumulative",
        "BINOMDIST"
    ],
    [
        3,
        5,
        0.5,
        false,
        "=BINOMDIST(A2, B2, C2, D2)"
    ],
    [
        3,
        5,
        0.5,
        true,
        "=BINOMDIST(A3, B3, C3, D3)"
    ],
    [
        2,
        10,
        0.1,
        true,
        "=BINOMDIST(A4, B4, C4, D4)"
    ],
    [
        4,
        8,
        0.7,
        false,
        "=BINOMDIST(A5, B5, C5, D5)"
    ]
]
            }]
        });
    }
}