PERCENTOF

What it does

PERCENTOF(data_subset, data_all)

PERCENTOF(data_subset,data_all) sums the first set and divides by the sum of the second. It is useful as a two-argument aggregation callback in GROUPBY and PIVOTBY. The result is a fraction; percentage cell formatting changes its display.

Try it

=PERCENTOF({2,3},{2,3,5,10}) returns 0.25, displayed as 25 percent with percentage formatting. The subset sums to five and the full set to twenty.

The denominator is a supplied comparison set. In a pivot cell with amount three, PERCENTOF(3,{3,5}) gives 0.375, while PERCENTOF(3,{3,7}) gives 0.3. The same numerator can therefore describe a column share or a row share. PIVOTBY's relative_to argument chooses that second set for a callback.

Quirks

The function does not verify that the first set is a literal subset of the second; supplied totals can produce a value above one or below zero. A zero denominator gives #DIV/0!. In recorded build-20527 controls, direct numeric text/logicals convert, while errors held in a reference or array collapse to #NUM!; a direct error retains its own code.

How to implement

Read each argument under its aggregate admission rules, accumulating each total with the established arithmetic-site addition. Divide subset total by whole total at a stored site and apply zero-denominator/error policy. In a grouping callback the caller supplies both the local values and the appropriate comparison set; the latter need not be the entire original data table. The example gives 5/20=0.25. Keep collection origin so direct and held error handling do not become indistinguishable.

Rust code

entries: &[CatalogueEntry {
    id: PERCENTOF,
    spelling: "PERCENTOF",
    notation: Notation::Call,
    arity: CallableArityShape::exact(2),
    parameters: &[
        Parameter::value_or_reference("data_subset"),
        Parameter::value_or_reference("data_all"),
    ],
    demand: ArgumentDemand::Eager,
    kernel: Kernel::Eager(percentof),
}

/// PERCENTOF (dc-h9u): the sum of data_subset divided by the sum of data_all, each summed as SUM
/// sums its one argument; #DIV/0! when the second sum is 0.
fn percentof(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(PERCENTOF, services);
    let mut sums = [0.0f64; 2];
    let mut read = Ok(());
    for (index, total) in sums.iter_mut().enumerate() {
        let one = std::slice::from_ref(&arguments[index]);
        let held = matches!(one[0].core_ref(), CoreRef::Reference(_) | CoreRef::Array(_));
        read = read
            .and_then(|()| direct_first(&site, one).map_err(|exit| reindex(exit, index)))
            .and_then(|()| {
                each_number(&site, one, services, Held::Numbers, &mut |n| {
                    *total = site.add(*total, n)?;
                    Ok(())
                })
                .map_err(|exit| match exit {
                    // An error a reference or an array holds is #NUM! (Excel 16.0.20527:
                    // PERCENTOF(C1,4) over C1 = #N/A, PERCENTOF({1,#N/A},4) are #NUM!).
                    Exit::Error(_) if held => Exit::Error(WorksheetErrorCode::Num),
                    other => reindex(other, index),
                })
            });
    }
    Site::finish(read.and_then(|()| site.number(site.divide(sums[0], sums[1])?)))
}

/// A refusal or fault naming argument 0 of a one-argument read, renamed to argument `index`.
fn reindex(exit: Exit, index: usize) -> Exit {
    match exit {
        Exit::Fault(FunctionFault::NotImplemented { id, argument: Some(0) }) => {
            Exit::Fault(FunctionFault::NotImplemented {
                id,
                argument: Some(index),
            })
        }
        other => other,
    }
}

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

Version differences

The current implementation comments retain COM readings from Excel 16.0 build 20527 on 30 October 2026. They cover direct versus held coercion/errors, including PERCENTOF(1,4)=0.25. No same-input comparison with another named build is recorded here. Microsoft’s opened page lists Excel for Microsoft 365; that is product availability, not a measured build comparison.

Alternatives

SUM(subset)/SUM(all) expresses the documented arithmetic relationship. GROUPBY and PIVOTBY supply grouping context.

Named variants

No named variants are recorded yet.