CEILING.MATH function
PRO
BASIC
The CEILING.MATH
function in Jspreadsheet Formulas Pro rounds a number upward to the nearest specified multiple of significance. Unlike CEILING, it offers additional control through the mode argument, allowing you to determine how negative numbers are handled—either away from or toward zero.
Documentation
Rounds a number up to the nearest multiple of a specified significance, with optional direction control for negative numbers.
Category
Math and trigonometry
Syntax
CEILING.MATH(number, [significance], [mode])
Parameter | Description |
---|---|
number |
The number to be rounded up. |
[significance] |
(Optional) The multiple to round to. Defaults to 1. |
[mode] |
(Optional) Controls rounding of negative numbers: 0 or omitted = away from zero; non-zero = toward zero. |
Behavior
The CEILING.MATH
function rounds a number up to the nearest integer or to the nearest multiple of significance. Here's how it handles different inputs:
-
Numbers: The function rounds up the given number to the nearest integer or multiple of significance.
-
Empty cells: If the
CEILING.MATH
function refers to an empty cell, it treats it as a zero. -
Text: If a text string is used as an argument, the function returns an error.
-
Booleans: If the function refers to a cell with a boolean value (
TRUE
orFALSE
), it treatsTRUE
as 1 andFALSE
as 0. -
Errors: If any cell referred to by the
CEILING.MATH
function contains a#VALUE!
,#REF!
,#NAME?
,#NUM!
,#DIV/0!
,#N/A
, or#ERROR!
, the function will return that error.
Common Errors
Error | Description |
---|---|
#VALUE! | This error is returned when one of the arguments is non-numeric or if any non-integer value is provided for the mode argument. |
#NUM! | This error is returned if the number is less than zero but the significance is greater than zero, or vice versa. |
Best practices
- Use mode = 0 (or omit it) for standard upward rounding, especially for financial use.
- Use mode ≠ 0 if you want negative numbers rounded toward zero.
- Make sure all arguments are numeric to prevent #VALUE! errors.
- Use decimal significance (e.g., 0.1, 0.25) to control rounding precision.
- Use IFERROR to handle errors
Usage
A few examples using the CEILING.MATH function.
CEILING.MATH(4.3, 1) // Returns 5
CEILING.MATH(-7.8, 2) // Returns -6 (rounded away from zero by default)
CEILING.MATH(-7.8, 2, 1) // Returns -8 (rounded toward zero due to mode ≠ 0)
CEILING.MATH(3.5, 0.1) // Returns 3.5 (already a multiple of 0.1)
CEILING.MATH(3.53, 0.1) // Returns 3.6
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('NWQyMDgwYWVkZWQ2ZTQ4ZDVmZDZiMzQxMDhjY2I0MWRkZjg2NzcwZjY4MTczZTlmZGY1Zjk2OGNlNGFmNWMwNjdlNzVkZWEzODhmYmEzNjQxMjM0MGQ2OTVmYzc2ZDljNWUzMDU4OTcxNTgyMzVkNTBiMzk1Y2M2ZGYzNTEwN2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Number",
"Significance",
"Rounded Up"
],
[
4.3,
1,
"=CEILING.MATH(A2,B2)"
],
[
-7.8,
2,
"=CEILING.MATH(A3,B3)"
],
[
3.47,
0.1,
"=CEILING.MATH(A4,B4)"
],
[
-2.3,
0.5,
"=CEILING.MATH(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('NWQyMDgwYWVkZWQ2ZTQ4ZDVmZDZiMzQxMDhjY2I0MWRkZjg2NzcwZjY4MTczZTlmZGY1Zjk2OGNlNGFmNWMwNjdlNzVkZWEzODhmYmEzNjQxMjM0MGQ2OTVmYzc2ZDljNWUzMDU4OTcxNTgyMzVkNTBiMzk1Y2M2ZGYzNTEwN2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Number",
"Significance",
"Rounded Up"
],
[
4.3,
1,
"=CEILING.MATH(A2,B2)"
],
[
-7.8,
2,
"=CEILING.MATH(A3,B3)"
],
[
3.47,
0.1,
"=CEILING.MATH(A4,B4)"
],
[
-2.3,
0.5,
"=CEILING.MATH(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('NWQyMDgwYWVkZWQ2ZTQ4ZDVmZDZiMzQxMDhjY2I0MWRkZjg2NzcwZjY4MTczZTlmZGY1Zjk2OGNlNGFmNWMwNjdlNzVkZWEzODhmYmEzNjQxMjM0MGQ2OTVmYzc2ZDljNWUzMDU4OTcxNTgyMzVkNTBiMzk1Y2M2ZGYzNTEwN2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Number",
"Significance",
"Rounded Up"
],
[
4.3,
1,
"=CEILING.MATH(A2,B2)"
],
[
-7.8,
2,
"=CEILING.MATH(A3,B3)"
],
[
3.47,
0.1,
"=CEILING.MATH(A4,B4)"
],
[
-2.3,
0.5,
"=CEILING.MATH(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('NWQyMDgwYWVkZWQ2ZTQ4ZDVmZDZiMzQxMDhjY2I0MWRkZjg2NzcwZjY4MTczZTlmZGY1Zjk2OGNlNGFmNWMwNjdlNzVkZWEzODhmYmEzNjQxMjM0MGQ2OTVmYzc2ZDljNWUzMDU4OTcxNTgyMzVkNTBiMzk1Y2M2ZGYzNTEwN2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Number",
"Significance",
"Rounded Up"
],
[
4.3,
1,
"=CEILING.MATH(A2,B2)"
],
[
-7.8,
2,
"=CEILING.MATH(A3,B3)"
],
[
3.47,
0.1,
"=CEILING.MATH(A4,B4)"
],
[
-2.3,
0.5,
"=CEILING.MATH(A5,B5)"
]
]
}]
});
}
}