FLOOR function

PRO BASIC

The FLOOR function in Jspreadsheet Formulas Pro is a tool you can use when you need to round a number down to the closest multiple of another specific number. For instance, if you want to round the number 7 down to the nearest multiple of 3, the FLOOR function will give you 6. This function is particularly helpful in instances where you want to maintain consistency with certain intervals or units.

Documentation

Rounds a number down to the nearest multiple of a specified factor.

Syntax

FLOOR(number, significance)

Parameter	Description
`number`	The number that you want to round down.
`significance`	The multiple to which you want to round. Default is 1.

Behavior

The FLOOR function rounds down a given number to the nearest multiple of a specified significance. It behaves as follows:

If the cell is empty, the function will return an error.
If the cell contains text, the function will return an error.
If the cell contains a boolean value, the function treats TRUE as 1 and FALSE as 0.
If the number is positive, the function will round down to the nearest multiple of significance.
If the number is negative, the function will round down toward negative infinity to the nearest multiple of significance.
The function handles errors by returning an error message. For example, if the significance is 0, the function returns a #DIV/0! error.

Common Errors

Error	Description
#VALUE!	This error is displayed when the input is non-numeric, such as text.
#DIV/0!	This error is returned when the significance is 0, as division by zero is not possible.
#NUM!	This error is returned when the significance is negative.

Best practices

Always ensure that the number and significance are numeric values for the FLOOR function to work correctly. Avoid using text or non-numeric values.

Be aware that the FLOOR function always rounds down, regardless of the number's decimal value. If you need to round to the nearest integer based on standard rounding rules, consider using the ROUND function instead.

Be cautious when using zero or negative values for the significance, as they may lead to errors.

Use absolute cell references if you want to apply the FLOOR function to multiple cells with the same significance. This avoids the need to manually enter the significance for each cell.

Usage

A few examples using the FLOOR function.

FLOOR(3.7, 1) returns 3  
FLOOR(6.8, 2) returns 6  
FLOOR(15.9, 5) returns 15  
FLOOR(-2.5, 1) returns -3  
FLOOR(12.345, 0.1) returns 12.3

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Price",
        "Round Down To",
        "Floored Price"
    ],
    [
        23.89,
        5,
        "=FLOOR(A2,B2)"
    ],
    [
        47.25,
        10,
        "=FLOOR(A3,B3)"
    ],
    [
        156.73,
        25,
        "=FLOOR(A4,B4)"
    ],
    [
        -8.3,
        -1,
        "=FLOOR(A5,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('NzMzZjljN2M3MDgxNjVhNDNkOGU1ZGJlYmVkYmUxNWM1MDNhOWZhZDViOGQxM2I5YzYxZmRlZjczYWI5M2JjMTk5Y2MyN2IwNzdjYTg4NWVjNGU2OWU4YjU1NWJhNjMyMDg5ODI2MDVkMTk2MDBlZmI4YTFiYjVjMjcxNTRlM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Price",
        "Round Down To",
        "Floored Price"
    ],
    [
        23.89,
        5,
        "=FLOOR(A2,B2)"
    ],
    [
        47.25,
        10,
        "=FLOOR(A3,B3)"
    ],
    [
        156.73,
        25,
        "=FLOOR(A4,B4)"
    ],
    [
        -8.3,
        -1,
        "=FLOOR(A5,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('NzMzZjljN2M3MDgxNjVhNDNkOGU1ZGJlYmVkYmUxNWM1MDNhOWZhZDViOGQxM2I5YzYxZmRlZjczYWI5M2JjMTk5Y2MyN2IwNzdjYTg4NWVjNGU2OWU4YjU1NWJhNjMyMDg5ODI2MDVkMTk2MDBlZmI4YTFiYjVjMjcxNTRlM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Price",
        "Round Down To",
        "Floored Price"
    ],
    [
        23.89,
        5,
        "=FLOOR(A2,B2)"
    ],
    [
        47.25,
        10,
        "=FLOOR(A3,B3)"
    ],
    [
        156.73,
        25,
        "=FLOOR(A4,B4)"
    ],
    [
        -8.3,
        -1,
        "=FLOOR(A5,B5)"
    ]
]

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

// 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: [
    [
        "Price",
        "Round Down To",
        "Floored Price"
    ],
    [
        23.89,
        5,
        "=FLOOR(A2,B2)"
    ],
    [
        47.25,
        10,
        "=FLOOR(A3,B3)"
    ],
    [
        156.73,
        25,
        "=FLOOR(A4,B4)"
    ],
    [
        -8.3,
        -1,
        "=FLOOR(A5,B5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub