 # How do I use custom rounding rules in Calc formulas?

How do I write a formula to round prices as follows:

[ x.00 to x.25 = (x-1).99 ] and [ x.26 to x.99 = x.99 ]

Thus, \$10.10 would be rounded to \$9.99 and \$24.56 would round to \$24.99 .

May be `=ABS(ROUND(A1+0.24)-0.01)`?

+1 @JohnSUN - I shouldn’t try a solution, if you are around. No, no - there must be 0.249, not 0.24 (with 3.26 cons 0.24 return wrong result 2.99 instead 3.99) So, `=ABS(ROUND(A1+0.249)-0.01)`

Spoken aside…
Why can’t we simply stop that nonsense?
Will AI help concerning the lack of NI?

Hello,

try this: `=IF(MOD(A1;1)<=0.25;ROUND(INT(A1)-0.01;2);ROUND(INT(A1)+0.99;2))` (assuming the value is in `A1`)

Tested on

``````Version: 7.1.0.3 / LibreOffice Community
Build ID: f6099ecf3d29644b5008cc8f48f42f4a40986e4c
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US
``````

Hope that helps

Thank you! Works perfectly.

@anon73440385: Considering that values being multiples of 0.01 are already rounded, while a real calculation of prices (using percenatges e.g.) may prinarily end up with something like 23,35876… we should set an upper or lower limit to compare by `<=` or `>=` and not set an “opposite limit” in addition.
If `<=x.25` shall be the limit, no `>= something` is needed if we have in mind a kind of dichotomy .
Try `=-INT(-A2-3/4)-1.01` based on the first inequality exemplified by the questioner - and get the rest automatically.
The topic of “rounding” and related functionality, however, is relevant. Concerning this part of the question, I found once again an attitude to ask as if numbers are only taken from an ascending sequence: nothing between “x.25” and “x.26” The general concept of rounding is based on one applicaple threshold per case, however. All common rounding or “unrounding” in this sense can very clearly and efficiently be implemented based on `INT()` without using attributes like “up”, “down”, “towards zero”, …