Math and trigonometry functions

MULTINOMIAL

Matches all 6 recorded answers

Recorded: 6 workbook cells. How we measure this · See the Rust code

Local names in 40 languages
LanguageName in Excel
ArabicMULTINOMIAL
BulgarianMULTINOMIAL
CzechMULTINOMIAL
DanishMULTINOMIAL
GermanMULTINOMIAL
GreekMULTINOMIAL
English (Australia)MULTINOMIAL
English (United Kingdom)MULTINOMIAL
English (United States)MULTINOMIAL
SpanishMULTINOMIAL
EstonianMULTINOMIAL
FinnishMULTINOMI
FrenchMULTINOMIALE
HebrewMULTINOMIAL
CroatianMULTINOMIAL
HungarianSZORHÁNYFAKT
IndonesianMULTINOMIAL
ItalianMULTINOMIAL
JapaneseMULTINOMIAL
KoreanMULTINOMIAL
LithuanianMULTINOMIAL
LatvianMULTINOMIAL
NorwegianMULTINOMIAL
DutchMULTINOMIAAL
PolishWIELOMIAN
Portuguese (Brazil)MULTINOMIAL
Portuguese (Portugal)POLINOMIAL
RomanianMULTINOMIAL
RussianМУЛЬТИНОМ
SlovakMULTINOMIAL
SlovenianMULTINOMIAL
Serbian (Latin)MULTINOMIAL
SwedishMULTINOMIAL
ThaiMULTINOMIAL
TurkishÇOKTERİMLİ işlevi
UkrainianMULTINOMIAL
VietnameseMULTINOMIAL
Chinese (Simplified)MULTINOMIAL
Chinese (Hong Kong)MULTINOMIAL
Chinese (Traditional)MULTINOMIAL

Names from Microsoft's support site.

What it does

MULTINOMIAL(number1, [number2], ...)

MULTINOMIAL(number1,[number2],...) counts arrangements of groups with the supplied sizes: factorial of the total divided by the product of each size’s factorial.

Try it

Input cells (optional)

Example: {"B2": 3, "B3": 4}. A1 holds the formula. Other cells need values before a formula can read them.

Enter a formula, then calculate.

Runs locally in your browser with OneCalc. Its answer may differ from Excel. Use English function names and commas between arguments.

=MULTINOMIAL(2,2,1) returns 30. Five items with two identical A items, two identical B items and one C item have 5!/(2!*2!*1!)=120/4 distinct arrangements.

Quirks

Negative arguments give #NUM!; nonnumeric arguments give #VALUE!. The current kernel truncates admitted sizes toward zero. Large factorial intermediates can overflow even when a ratio-based calculation is practical. Numerical publication near large integers needs its own compatibility evidence.

How to implement

For a mathematical integer implementation, start total=0,result=1. For each nonnegative integer group size c, multiply the result by choose(total+c,c) and increase total by c. Compute each choose with exact integer arithmetic or a cancellation-based recurrence. The example multiplies choose(2,2)=1, choose(4,2)=6, and choose(5,1)=5, yielding thirty. This avoids separately constructing all factorials. The current product also contains log/Lanczos publication candidates and bounded rounding heuristics; the exact-integer recipe is not an identification of those Excel last-bit choices.

Rust code

Show the CalcFunc source of this function

Quoted from the public repository DnaCalc/DnaCalc, crates calc-func and calc-fml, at commit 9e1884369a67. Comments are the source's own. A kernel shared with other functions appears once, on its module page.

MULTINOMIAL · 21 lines · calc-func/src/catalogue/math.rs on GitHub
40 multinomial "MULTINOMIAL" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    // Admission runs across every value before the numeric domain is checked.
    // Held blank cells are skipped; logicals are rejected in every origin.
    let mut numbers = Vec::new();
    values(s, a, 0, a.len(), sv, &mut |held, value| {
        if held && matches!(value.core_ref(), CoreRef::Empty) {
            return Ok(());
        }
        reject_logical(value)?;
        numbers.push(s.number_argument(0, value)?);
        Ok(())
    })?;
    let mut items = Vec::with_capacity(numbers.len());
    for n in numbers {
        if !n.is_finite() || n < 0.0 {
            return fail(WorksheetErrorCode::Num);
        }
        items.push(n.trunc() as i64);
    }
    out(s, k::multinomial::multinomial_kernel(&items))
}
factorial_of_int · 6 lines · calc-func/src/ported/factorial_common.rs on GitHub
pub fn factorial_of_int(n: i64) -> f64 {
    if n <= 1 {
        return 1.0;
    }
    (2..=n).fold(1.0, |acc, item| acc * item as f64)
}
next_down · 11 lines · calc-func/src/ported/multinomial.rs on GitHub
fn next_down(x: f64) -> f64 {
    if x.is_nan() || x == f64::NEG_INFINITY {
        x
    } else if x == 0.0 {
        -f64::from_bits(1)
    } else if x > 0.0 {
        f64::from_bits(x.to_bits() - 1)
    } else {
        f64::from_bits(x.to_bits() + 1)
    }
}
step_down · 6 lines · calc-func/src/ported/multinomial.rs on GitHub
fn step_down(mut x: f64, steps: u64) -> f64 {
    for _ in 0..steps {
        x = next_down(x);
    }
    x
}
ulp_distance · 3 lines · calc-func/src/ported/multinomial.rs on GitHub
fn ulp_distance(lhs: f64, rhs: f64) -> u64 {
    lhs.to_bits().abs_diff(rhs.to_bits())
}
multinomial_positive_items · 3 lines · calc-func/src/ported/multinomial.rs on GitHub
fn multinomial_positive_items(items: &[i64]) -> Vec<i64> {
    items.iter().copied().filter(|item| *item > 0).collect()
}
positive_items_are_consecutive · 8 lines · calc-func/src/ported/multinomial.rs on GitHub
fn positive_items_are_consecutive(positive_items: &[i64]) -> bool {
    if positive_items.len() < 2 {
        return false;
    }
    let mut sorted = positive_items.to_vec();
    sorted.sort_unstable();
    sorted.windows(2).all(|window| window[1] == window[0] + 1)
}
multinomial_exact_candidate · 7 lines · calc-func/src/ported/multinomial.rs on GitHub
fn multinomial_exact_candidate(positive_items: &[i64]) -> f64 {
    let total = positive_items.iter().sum::<i64>();
    let denominator = positive_items
        .iter()
        .fold(1.0, |acc, item| acc * factorial_of_int(*item));
    factorial_of_int(total) / denominator
}
multinomial_log_factorial_candidate · 9 lines · calc-func/src/ported/multinomial.rs on GitHub
fn multinomial_log_factorial_candidate(positive_items: &[i64]) -> f64 {
    let total = positive_items.iter().sum::<i64>();
    let numerator_ln = crt_log(factorial_of_int(total));
    let denominator_ln = positive_items
        .iter()
        .map(|item| crt_log(factorial_of_int(*item)))
        .sum::<f64>();
    crt_exp(numerator_ln - denominator_ln)
}
lanczos_ln_gamma · 10 lines · calc-func/src/ported/multinomial.rs on GitHub
fn lanczos_ln_gamma(x: f64) -> f64 {
    let z = x - 1.0;
    let mut acc = MULTINOMIAL_LANCZOS_COEFFS[0];
    for (index, coeff) in MULTINOMIAL_LANCZOS_COEFFS.iter().enumerate().skip(1) {
        acc += coeff / (z + index as f64);
    }

    let t = z + MULTINOMIAL_LANCZOS_G + 0.5;
    0.5 * crt_log(2.0 * std::f64::consts::PI) + (z + 0.5) * crt_log(t) - t + crt_log(acc)
}
multinomial_descending_log_numerator_lanczos_denominator_candidate · 14 lines · calc-func/src/ported/multinomial.rs on GitHub
fn multinomial_descending_log_numerator_lanczos_denominator_candidate(
    positive_items: &[i64],
) -> f64 {
    let total = positive_items.iter().sum::<i64>();
    let numerator_ln = (2..=total)
        .rev()
        .map(|value| crt_log(value as f64))
        .sum::<f64>();
    let denominator_ln = positive_items
        .iter()
        .map(|item| lanczos_ln_gamma((*item + 1) as f64))
        .sum::<f64>();
    crt_exp(numerator_ln - denominator_ln)
}
multinomial_lanczos_candidate · 9 lines · calc-func/src/ported/multinomial.rs on GitHub
fn multinomial_lanczos_candidate(positive_items: &[i64]) -> f64 {
    let total = positive_items.iter().sum::<i64>();
    let numerator_ln = lanczos_ln_gamma((total + 1) as f64);
    let denominator_ln = positive_items
        .iter()
        .map(|item| lanczos_ln_gamma((*item + 1) as f64))
        .sum::<f64>();
    crt_exp(numerator_ln - denominator_ln)
}
multinomial_publication_candidate · 58 lines · calc-func/src/ported/multinomial.rs on GitHub
fn multinomial_publication_candidate(positive_items: &[i64], exact_candidate: f64) -> f64 {
    let total = positive_items.iter().sum::<i64>();

    match positive_items.len() {
        0 | 1 => exact_candidate,
        2 => {
            if total < 5 {
                return exact_candidate;
            }

            let candidate = multinomial_log_factorial_candidate(positive_items);
            if candidate.is_finite() && candidate < exact_candidate {
                let floor = step_down(
                    exact_candidate,
                    MULTINOMIAL_TWO_POSITIVE_PUBLICATION_ULP_CLAMP,
                );
                candidate.max(floor)
            } else {
                exact_candidate
            }
        }
        3 => {
            if total < 9 || !positive_items_are_consecutive(positive_items) {
                return exact_candidate;
            }

            let candidate = multinomial_log_factorial_candidate(positive_items);
            if candidate.is_finite() && candidate < exact_candidate {
                candidate
            } else {
                exact_candidate
            }
        }
        _ => {
            if total < 10 || !positive_items_are_consecutive(positive_items) {
                return exact_candidate;
            }

            let mut best = exact_candidate;
            let mut best_distance = MULTINOMIAL_MULTI_POSITIVE_PUBLICATION_ULP_RADIUS + 1;
            for candidate in [
                multinomial_descending_log_numerator_lanczos_denominator_candidate(positive_items),
                multinomial_lanczos_candidate(positive_items),
            ] {
                if candidate.is_finite() {
                    let distance = ulp_distance(candidate, exact_candidate);
                    if distance <= MULTINOMIAL_MULTI_POSITIVE_PUBLICATION_ULP_RADIUS
                        && distance < best_distance
                    {
                        best = candidate;
                        best_distance = distance;
                    }
                }
            }
            best
        }
    }
}
multinomial_kernel · 11 lines · calc-func/src/ported/multinomial.rs on GitHub
pub fn multinomial_kernel(items: &[i64]) -> Result<f64, WorksheetErrorCode> {
    let positive_items = multinomial_positive_items(items);
    let exact_candidate = multinomial_exact_candidate(&positive_items);
    if !exact_candidate.is_finite() {
        return Ok(exact_candidate);
    }
    Ok(multinomial_publication_candidate(
        &positive_items,
        exact_candidate,
    ))
}

Argument and result plumbing

No implementation has been published with a Handbook test suite for this function.

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

COMBIN chooses one group; FACT is the factorial primitive; PERMUT distinguishes ordered selections.

Named variants

No named variants are recorded yet.

Suggest a correction or contribute an implementation · For agents