CEILING.PRECISE function

PRO BASIC

The CEILING.PRECISE function in Jspreadsheet Formulas Pro rounds numbers upward to the nearest multiple of a specified significance, regardless of the sign of the number. Unlike traditional CEILING, it always rounds away from zero, and does not use 'bankers’ rounding'.

Documentation

Rounds a number up to the nearest multiple of a specified significance, using bankers' rounding (round half up).

Syntax

CEILING.PRECISE(number, [significance])

Parameter	Description
`number`	The number to be rounded up.
`[significance]`	Optional. The multiple to which you want to round the number. If omitted, the default value is 1.

Behavior

The CEILING.PRECISE function rounds up a number to the nearest integer or to the nearest multiple of significance. The function takes two arguments: number and significance. If the significance is not provided, the function assumes it to be 1.

Here's how it handles different types of values:

Empty cells: If the number or significance cell is empty, the function treats it as zero.
Text: If the number or significance is a text string, the function returns an error.
Booleans: If the number or significance is a boolean, it gets implicitly converted to a numeric value (True to 1 and False to 0).
Errors: If any of the arguments is an error, the function propagates the error.
Negative numbers: The CEILING.PRECISE function always rounds upward away from zero, regardless of the sign of significance. That means even if the number is negative, it rounds toward zero if needed to reach the next multiple upward.

Common Errors

Error	Description
#VALUE!	This error occurs when either of the arguments is non-numeric, such as a text string.
#NUM!	This error occurs when the `significance` is zero.
#DIV/0!	This error occurs when both `number` and `significance` are zero.

Best practices

Always ensure that the inputs are numeric. If there's a chance they might not be, use error handling functions to manage possible errors.

Be aware of the behavior of the function with negative numbers. The function rounds away from zero, which might not be the expected behavior in all scenarios.

When dealing with financial calculations, be careful with the significance parameter to ensure the rounding aligns with the financial rules in place.

If you want the function to always round up regardless of the sign of the number, consider using the CEILING function instead.

Usage

A few examples using the CEILING.PRECISE function.

CEILING.PRECISE(4.3, 1) returns 5  
CEILING.PRECISE(7.8, 0.5) returns 8  
CEILING.PRECISE(-2.5, -2) returns -2

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Price",
        "Significance",
        "Rounded Up Price"
    ],
    [
        4.3,
        1,
        "=CEILING.PRECISE(A2,B2)"
    ],
    [
        7.8,
        0.5,
        "=CEILING.PRECISE(A3,B3)"
    ],
    [
        -2.5,
        -2,
        "=CEILING.PRECISE(A4,B4)"
    ],
    [
        23.67,
        5,
        "=CEILING.PRECISE(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('NTBiYjNlNWMzNmFmY2RmMzQ3ZDMyN2Q2ZjM0NmJlYjcwNGNmZDcyMThmMDY3NjJiMGM2ZGFiOGMzMTY1NzgxNWY2Zjc0MGNjYjNkZDcyY2JhNjk3NDI1M2I2YzViMjA1NjZmNjI4MDdhZmExOTcxMmRiOGJhZWQyNjNlZDI3ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZeU1qRTVNRE13TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0ltTm9ZWEowY3lJc0ltWnZjbTF6SWl3aVptOXliWFZzWVNJc0luQmhjbk5sY2lJc0luSmxibVJsY2lJc0ltTnZiVzFsYm5Seklpd2lhVzF3YjNKMFpYSWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0lzSW5CeWFXNTBJaXdpYzJobFpYUnpJaXdpWTJ4cFpXNTBJaXdpYzJWeWRtVnlJaXdpYzJoaGNHVnpJaXdpWm05eWJXRjBJbDBzSW1SbGJXOGlPblJ5ZFdWOQ==');

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

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

    // Worksheet data
    const data = [
    [
        "Price",
        "Significance",
        "Rounded Up Price"
    ],
    [
        4.3,
        1,
        "=CEILING.PRECISE(A2,B2)"
    ],
    [
        7.8,
        0.5,
        "=CEILING.PRECISE(A3,B3)"
    ],
    [
        -2.5,
        -2,
        "=CEILING.PRECISE(A4,B4)"
    ],
    [
        23.67,
        5,
        "=CEILING.PRECISE(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('NTBiYjNlNWMzNmFmY2RmMzQ3ZDMyN2Q2ZjM0NmJlYjcwNGNmZDcyMThmMDY3NjJiMGM2ZGFiOGMzMTY1NzgxNWY2Zjc0MGNjYjNkZDcyY2JhNjk3NDI1M2I2YzViMjA1NjZmNjI4MDdhZmExOTcxMmRiOGJhZWQyNjNlZDI3ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZeU1qRTVNRE13TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0ltTm9ZWEowY3lJc0ltWnZjbTF6SWl3aVptOXliWFZzWVNJc0luQmhjbk5sY2lJc0luSmxibVJsY2lJc0ltTnZiVzFsYm5Seklpd2lhVzF3YjNKMFpYSWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0lzSW5CeWFXNTBJaXdpYzJobFpYUnpJaXdpWTJ4cFpXNTBJaXdpYzJWeWRtVnlJaXdpYzJoaGNHVnpJaXdpWm05eWJXRjBJbDBzSW1SbGJXOGlPblJ5ZFdWOQ==');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Price",
        "Significance",
        "Rounded Up Price"
    ],
    [
        4.3,
        1,
        "=CEILING.PRECISE(A2,B2)"
    ],
    [
        7.8,
        0.5,
        "=CEILING.PRECISE(A3,B3)"
    ],
    [
        -2.5,
        -2,
        "=CEILING.PRECISE(A4,B4)"
    ],
    [
        23.67,
        5,
        "=CEILING.PRECISE(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('NTBiYjNlNWMzNmFmY2RmMzQ3ZDMyN2Q2ZjM0NmJlYjcwNGNmZDcyMThmMDY3NjJiMGM2ZGFiOGMzMTY1NzgxNWY2Zjc0MGNjYjNkZDcyY2JhNjk3NDI1M2I2YzViMjA1NjZmNjI4MDdhZmExOTcxMmRiOGJhZWQyNjNlZDI3ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZeU1qRTVNRE13TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0ltTm9ZWEowY3lJc0ltWnZjbTF6SWl3aVptOXliWFZzWVNJc0luQmhjbk5sY2lJc0luSmxibVJsY2lJc0ltTnZiVzFsYm5Seklpd2lhVzF3YjNKMFpYSWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0lzSW5CeWFXNTBJaXdpYzJobFpYUnpJaXdpWTJ4cFpXNTBJaXdpYzJWeWRtVnlJaXdpYzJoaGNHVnpJaXdpWm05eWJXRjBJbDBzSW1SbGJXOGlPblJ5ZFdWOQ==');

// 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",
        "Significance",
        "Rounded Up Price"
    ],
    [
        4.3,
        1,
        "=CEILING.PRECISE(A2,B2)"
    ],
    [
        7.8,
        0.5,
        "=CEILING.PRECISE(A3,B3)"
    ],
    [
        -2.5,
        -2,
        "=CEILING.PRECISE(A4,B4)"
    ],
    [
        23.67,
        5,
        "=CEILING.PRECISE(A5,B5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub