PERCENTOF function

PRO

The PERCENTOF function in Jspreadsheet Formulas Pro returns the share that a subset of values represents within a larger data set. It adds the values of the subset, adds the values of the full set and divides one by the other, which replaces the usual SUM(part)/SUM(total) pattern with a single readable call. It is also accepted as the aggregation function of GROUPBY and PIVOTBY, where it returns the share of each group.

Documentation

Returns the sum of a subset of values divided by the sum of all values, as a decimal fraction.

Syntax

PERCENTOF(data_subset, data_all)

Parameter	Description
`data_subset`	The value, range or array whose total is the numerator.
`data_all`	The value, range or array whose total is the denominator.

Behavior

The PERCENTOF function sums both arguments and returns SUM(data_subset) / SUM(data_all) as a decimal number, so 30% is returned as 0.3.

Text inside a range is ignored. A text value passed directly as data_subset counts as 0.
Numeric text passed directly, such as "5", is converted to a number.
Empty cells count as 0 in both arguments.
The logical value TRUE passed directly counts as 1. Logical values inside a range or array are ignored.
Negative values are allowed and produce a negative result.
The subset does not have to be part of data_all; the function simply divides the two sums.
Used as the function of GROUPBY or PIVOTBY, it returns the share of each group relative to the total of all groups.

Common Errors

Error	Description
#DIV/0!	Returned when `data_all` sums to zero, for example when it is empty or contains only text.

Best practices

Apply a percentage format to the cell or wrap the result with TEXT(..., "0%"), since the function returns a decimal fraction.

Anchor data_all with absolute references, such as B$2:B$6, when the formula is copied down a column of shares.

Use it as the function argument of GROUPBY to get the share of each category in one step instead of dividing a SUM summary afterwards.

Check that data_all contains numbers. A range of text produces a #DIV/0! error rather than 0.

Usage

A few examples using the PERCENTOF function.

// A1:A4 = 10, 20, 30, 40
PERCENTOF(A1:A2, A1:A4)
// Returns 0.3

PERCENTOF(A1, A1:A4)
// Returns 0.1

PERCENTOF(25, 100)
// Returns 0.25

TEXT(PERCENTOF(A1:A2, A1:A4), "0%")
// Returns "30%"

GROUPBY(B2:B6, C2:C6, PERCENTOF)
// Share of each group in column B relative to the total of column C

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Region",
        "Sales",
        "Share"
    ],
    [
        "North",
        1200,
        "=PERCENTOF(B2, B$2:B$5)"
    ],
    [
        "South",
        800,
        "=PERCENTOF(B3, B$2:B$5)"
    ],
    [
        "East",
        1500,
        "=PERCENTOF(B4, B$2:B$5)"
    ],
    [
        "West",
        500,
        "=PERCENTOF(B5, B$2:B$5)"
    ],
    [
        "Total",
        "=SUM(B2:B5)",
        "=PERCENTOF(B2:B5, B2:B5)"
    ]
]
  }]
});
</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('OWZhNzEwYjA1NmUwMWU5MDMzOGI0NWE2YWVmZjU5NWJiOGFjN2Q0OGE3NjY2ZGRjYzAwMjA0MDZiZWVlYzRjODdjYmVmNGMwYmJiMDBkNmNiZmQwNGVlODM3YWM4NGJjZGQxOTVjYTM4ZjYxZmI3MmQ1MzMyMTg1NWZlY2ExM2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNamt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "Region",
        "Sales",
        "Share"
    ],
    [
        "North",
        1200,
        "=PERCENTOF(B2, B$2:B$5)"
    ],
    [
        "South",
        800,
        "=PERCENTOF(B3, B$2:B$5)"
    ],
    [
        "East",
        1500,
        "=PERCENTOF(B4, B$2:B$5)"
    ],
    [
        "West",
        500,
        "=PERCENTOF(B5, B$2:B$5)"
    ],
    [
        "Total",
        "=SUM(B2:B5)",
        "=PERCENTOF(B2:B5, B2:B5)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Region",
        "Sales",
        "Share"
    ],
    [
        "North",
        1200,
        "=PERCENTOF(B2, B$2:B$5)"
    ],
    [
        "South",
        800,
        "=PERCENTOF(B3, B$2:B$5)"
    ],
    [
        "East",
        1500,
        "=PERCENTOF(B4, B$2:B$5)"
    ],
    [
        "West",
        500,
        "=PERCENTOF(B5, B$2:B$5)"
    ],
    [
        "Total",
        "=SUM(B2:B5)",
        "=PERCENTOF(B2:B5, B2:B5)"
    ]
]

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

// 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: [
    [
        "Region",
        "Sales",
        "Share"
    ],
    [
        "North",
        1200,
        "=PERCENTOF(B2, B$2:B$5)"
    ],
    [
        "South",
        800,
        "=PERCENTOF(B3, B$2:B$5)"
    ],
    [
        "East",
        1500,
        "=PERCENTOF(B4, B$2:B$5)"
    ],
    [
        "West",
        500,
        "=PERCENTOF(B5, B$2:B$5)"
    ],
    [
        "Total",
        "=SUM(B2:B5)",
        "=PERCENTOF(B2:B5, B2:B5)"
    ]
]
            }]
        });
    }
}