DocumentationFormulasFunctionsF test

F.TEST function

PRO

The F.TEST function in Jspreadsheet Formulas Pro returns the two-tailed probability that the variances of two samples are not significantly different. It is used before a t-test to decide whether the equal-variance assumption holds. A small result, such as below 0.05, indicates that the variances differ. It returns the same value as the compatibility function FTEST.

Documentation

Returns the result of an F-test, the two-tailed probability that the variances in two data sets are not significantly different.

Category

Statistical

Syntax

F.TEST(array1, array2)

Parameter Description
array1 The first range or array of numeric data.
array2 The second range or array of numeric data.

Behavior

The F.TEST function computes the ratio of the two sample variances and returns the two-tailed probability of observing that ratio when the population variances are equal.

  • Each array needs at least two numeric values. Otherwise the variance cannot be computed and the function returns #DIV/0!.
  • Text and logical values inside the ranges are ignored. An argument that contains no numbers returns #DIV/0!.
  • Comparing a range with itself returns 1.
  • The arrays do not need to have the same number of values.

Common Errors

Error Description
#DIV/0! Returned when either array has fewer than two numeric values, including ranges that contain only text.

Best practices

  • Make sure both ranges contain only the observations, without headers or totals, since text is ignored silently.
  • Make sure each range has at least two numeric values, otherwise the result is #DIV/0!.
  • Use absolute references for both ranges when the formula is copied to other cells.
  • Wrap the call with IFERROR when the ranges may be partially empty.

Usage

A few examples using the F.TEST function.

// A2:A6 = 2, 4, 6, 8, 10 and B2:B6 = 1, 3, 5, 7, 11
F.TEST(A2:A6, B2:B6)
// Returns 0.713303, the variances are not significantly different

F.TEST(A2:A6, A2:A6)
// Returns 1

F.TEST(A2:A2, B2:B6)
// Returns #DIV/0! because the first array has a single value

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "F.TEST",
        "=F.TEST(A2:A6, B2:B6)"
    ],
    [
        4,
        3,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S B",
        "=VAR.S(B2:B6)"
    ],
    [
        8,
        7
    ],
    [
        10,
        11
    ]
]
  }]
});
</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('MWYwMDg4Y2JkYzk1ZDkxNjg4ZGYwOTIyOTEyOWQyZDg4NzliYjM0NjI5MmQ0NTRjNjQxMjhhMjA1MjE3ZWE1ZDk4ODg2NDNlZWNiM2NkYmZiMzEwNWRkZDQzNTliOTI5OGVjYjFmOTcyM2ZhNWRkNzQ2YWYzNDQ5ZDJjZWZmNjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalkyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "F.TEST",
        "=F.TEST(A2:A6, B2:B6)"
    ],
    [
        4,
        3,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S B",
        "=VAR.S(B2:B6)"
    ],
    [
        8,
        7
    ],
    [
        10,
        11
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "F.TEST",
        "=F.TEST(A2:A6, B2:B6)"
    ],
    [
        4,
        3,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S B",
        "=VAR.S(B2:B6)"
    ],
    [
        8,
        7
    ],
    [
        10,
        11
    ]
]

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

// 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: [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "F.TEST",
        "=F.TEST(A2:A6, B2:B6)"
    ],
    [
        4,
        3,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S B",
        "=VAR.S(B2:B6)"
    ],
    [
        8,
        7
    ],
    [
        10,
        11
    ]
]
            }]
        });
    }
}