CHISQ.TEST function
PRO
The CHISQ.TEST function in Jspreadsheet Formulas Pro is a statistical tool that checks the independence of two sets of data. Essentially, it helps determine if there is a significant association between the two variables in your data sets. It does this by comparing your observed data with what would be expected if the variables were independent of each other. The function returns a value between 0 and 1, where a lower value indicates a higher likelihood that your data sets are not independent.
Documentation
Returns the test for independence.
Category
Statistical
Syntax
CHISQ.TEST(actual_range, expected_range)
| Parameter | Description |
|---|---|
actual_range |
A range of cells containing the observed frequency counts. Each cell must contain a non-negative number. |
expected_range |
A range of cells containing the expected frequency counts. Each cell must contain a non-negative number. |
Behavior
The 'CHISQ.TEST' function is used to perform a chi-square test on two ranges of data. It's used to analyze the association between two categorical variables. It returns the probability associated with a chi-square distribution. he function returns a p-value between 0 and 1. A lower value suggests a stronger indication that the variables are not independent (i.e., there is a statistically significant association).
-
When given two ranges of equal size, 'CHISQ.TEST' will perform the test on corresponding values in the ranges.
-
If the ranges contain empty cells, those cells are ignored and the pair of values is not included in the calculation.
-
If the ranges contain text or boolean values, the function will return a '#VALUE!' error.
-
The function requires that the ranges contain only positive numbers. If a range contains a negative number, the function will return a '#NUM!' error.
-
If the observed or expected frequencies are too small (generally less than 5), the chi-square test may not be valid and could give inaccurate results.
Common Errors
| Error | Description |
|---|---|
| #VALUE! | Returned when one or both input ranges contain non-numeric values (text or booleans). |
| #NUM! | This error is returned when one or both of the input ranges contain negative numbers or when the ranges are of different sizes. |
| #N/A | Returned when the input ranges are empty or insufficient to compute a result. |
Best practices
- Always ensure that your data ranges are of the same size. Mismatched ranges will result in a '#NUM!' error.
- Ensure that your data ranges contain only positive numerical values. Text, boolean values, or negative numbers will cause the function to return an error.
- The CHISQ.TEST function is most accurate when used with larger sample sizes. If your sample size is small, consider using a different statistical test.
- Be wary of expected frequencies that are too small, as the chi-square test may not give accurate results in these cases.
Usage
A few examples using the CHISQ.TEST function.
CHISQ.TEST(A2:B3, C2:D3) // Compares observed (A2:B3) and expected (C2:D3) frequencies
CHISQ.TEST({10, 20; 30, 40}, {15, 15; 35, 35}) // Inline array version
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('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Observed",
"Male",
"Female"
],
[
"Prefer A",
20,
30
],
[
"Prefer B",
25,
15
],
[
"Expected",
"Male",
"Female"
],
[
"Prefer A",
22.5,
27.5
],
[
"Prefer B",
22.5,
17.5
],
[
"Chi-Square Test",
"=CHISQ.TEST(B2:C3,B5:C6)"
]
]
}]
});
</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('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Observed",
"Male",
"Female"
],
[
"Prefer A",
20,
30
],
[
"Prefer B",
25,
15
],
[
"Expected",
"Male",
"Female"
],
[
"Prefer A",
22.5,
27.5
],
[
"Prefer B",
22.5,
17.5
],
[
"Chi-Square Test",
"=CHISQ.TEST(B2:C3,B5:C6)"
]
];
// 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('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Observed",
"Male",
"Female"
],
[
"Prefer A",
20,
30
],
[
"Prefer B",
25,
15
],
[
"Expected",
"Male",
"Female"
],
[
"Prefer A",
22.5,
27.5
],
[
"Prefer B",
22.5,
17.5
],
[
"Chi-Square Test",
"=CHISQ.TEST(B2:C3,B5:C6)"
]
]
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('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Observed",
"Male",
"Female"
],
[
"Prefer A",
20,
30
],
[
"Prefer B",
25,
15
],
[
"Expected",
"Male",
"Female"
],
[
"Prefer A",
22.5,
27.5
],
[
"Prefer B",
22.5,
17.5
],
[
"Chi-Square Test",
"=CHISQ.TEST(B2:C3,B5:C6)"
]
]
}]
});
}
}