QUARTILE.INC function

PRO BASIC

The QUARTILE.INC is a function in Jspreadsheet Formulas Pro that computes the inclusive quartile of a dataset. This function can return the minimum, first quartile (25th percentile), median (50th percentile), third quartile (75th percentile), or maximum, making it useful for statistical analysis and data distribution insights. It uses a slightly different calculation method compared to the standard QUARTILE function. Using this function, you can easily understand and analyze the distribution of your data.

Documentation

Calculates the inclusive quartile of a dataset, which is a value that separates the lowest 25% from the highest 75% of values. This function uses a slightly different calculation than the QUARTILE function.

Syntax

QUARTILE.INC(array, quart)

Parameter	Description
`array`	The array or range of data for which to determine the quartile.
`quart`	The quartile to return. 0 returns the minimum value, 1 returns the first quartile (25th percentile), 2 returns the median (50th percentile), 3 returns the third quartile (75th percentile), and 4 returns the maximum value.

Behavior

The QUARTILE.INC function calculates the quartile (each of four equal groups) inclusive of a data set based on percentile values from 0 to 1, inclusive.

Here's how it handles different values:

Empty cells: Any empty cells are ignored by the QUARTILE.INC function.
Text: If the array or cell reference points to a text, the function will return a #VALUE! error.
Booleans: Boolean values are treated as 1 (TRUE) and 0 (FALSE).
Errors: If any cell in the dataset contains an error, the QUARTILE.INC function will return that error.
Numbers: The QUARTILE.INC function operates on numeric values. Any non-numeric value will cause a #VALUE! error.

Common Errors

Error	Description
#NUM!	This error occurs if the `quart` value is less than 0, greater than 4, or not an integer. It also occurs if the dataset is empty.
#VALUE!	This error occurs if the `quart` argument is non-numeric.
#N/A	It will appear if the dataset is empty.

Best practices

Always make sure that your dataset only contains numeric values to avoid the #VALUE! error.

Ensure that the quart value you're trying to calculate is an integer between 0 and 4 inclusive, otherwise you'll get a #NUM! error.

Be careful with boolean values in your dataset, as they are treated as 1 (TRUE) and 0 (FALSE).

It's a good practice to clean your data and handle any possible errors before using the QUARTILE.INC function to get accurate results.

Usage

A few examples using the QUARTILE.INC function.

QUARTILE.INC(A1:A10,1) returns the first quartile (25th percentile) for the range A1:A10 using the inclusive method  
QUARTILE.INC(B2:B20,2) returns the median (50th percentile) for the range B2:B20 using the inclusive method  
QUARTILE.INC(C1:C100,3) returns the third quartile (75th percentile) for the range C1:C100 using the inclusive method  
QUARTILE.INC(D5:D50,0) returns the minimum value in the range D5:D50  
QUARTILE.INC(E1:E200,4) returns the maximum value in the range E1:E200

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Student",
        "Test Score"
    ],
    [
        "Alice",
        78
    ],
    [
        "Bob",
        85
    ],
    [
        "Carol",
        92
    ],
    [
        "David",
        67
    ],
    [
        "Eve",
        89
    ],
    [
        "Frank",
        74
    ],
    [
        "Grace",
        96
    ],
    [
        "Henry",
        83
    ],
    [
        "Q1 (25th percentile)",
        "=QUARTILE.INC(B2:B9,1)"
    ],
    [
        "Q2 (Median)",
        "=QUARTILE.INC(B2:B9,2)"
    ],
    [
        "Q3 (75th percentile)",
        "=QUARTILE.INC(B2:B9,3)"
    ]
]
  }]
});
</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('OGI2ZWM0ZDRhNDExYTk0NmM0Y2EzNDcxYTFhNGQ4MWQ0ZTBhNTUzZjhiNGI3ZDMyM2MyODg4ZTg4ZmIxNTAxYTAzZDJhNWYyOThmNTIyOGI4ZTUzM2ExNjIwNDA3NmM2NDUyN2RhYTRmYjA0MWVmOWQ3M2Y0NzUyMDU2ZWMxNWQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56ZzJNRFUxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Student",
        "Test Score"
    ],
    [
        "Alice",
        78
    ],
    [
        "Bob",
        85
    ],
    [
        "Carol",
        92
    ],
    [
        "David",
        67
    ],
    [
        "Eve",
        89
    ],
    [
        "Frank",
        74
    ],
    [
        "Grace",
        96
    ],
    [
        "Henry",
        83
    ],
    [
        "Q1 (25th percentile)",
        "=QUARTILE.INC(B2:B9,1)"
    ],
    [
        "Q2 (Median)",
        "=QUARTILE.INC(B2:B9,2)"
    ],
    [
        "Q3 (75th percentile)",
        "=QUARTILE.INC(B2:B9,3)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Student",
        "Test Score"
    ],
    [
        "Alice",
        78
    ],
    [
        "Bob",
        85
    ],
    [
        "Carol",
        92
    ],
    [
        "David",
        67
    ],
    [
        "Eve",
        89
    ],
    [
        "Frank",
        74
    ],
    [
        "Grace",
        96
    ],
    [
        "Henry",
        83
    ],
    [
        "Q1 (25th percentile)",
        "=QUARTILE.INC(B2:B9,1)"
    ],
    [
        "Q2 (Median)",
        "=QUARTILE.INC(B2:B9,2)"
    ],
    [
        "Q3 (75th percentile)",
        "=QUARTILE.INC(B2:B9,3)"
    ]
]

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

// 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: [
    [
        "Student",
        "Test Score"
    ],
    [
        "Alice",
        78
    ],
    [
        "Bob",
        85
    ],
    [
        "Carol",
        92
    ],
    [
        "David",
        67
    ],
    [
        "Eve",
        89
    ],
    [
        "Frank",
        74
    ],
    [
        "Grace",
        96
    ],
    [
        "Henry",
        83
    ],
    [
        "Q1 (25th percentile)",
        "=QUARTILE.INC(B2:B9,1)"
    ],
    [
        "Q2 (Median)",
        "=QUARTILE.INC(B2:B9,2)"
    ],
    [
        "Q3 (75th percentile)",
        "=QUARTILE.INC(B2:B9,3)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub