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)?
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
Calc: threaded
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.
(Tell if I’m wrong, please.)
@Lupp - no idea why I should discuss a solution, which provides the same result with less resources. And in that sense yours is obviously the better one. I’m not driven to find the perfect, most efficient solution but to find and provide a working one (given the question, and even if being aware, that it will fail with data OP doesn’t think about at time of asking). In contrary to other users here, I’m tired making assumptions and to clarify possible constraints. This leads to endless discussions and if … then … elseif … responses (e.g. what about values <= 0.25?)
I didn’t want to impose a discussion on you. But since I meanwhile was 6 times notified about your editing of the above comment, I learned that my own comment challenged you much more than expected.
Now I see that I should not have addressed it namely.
My reason to do so was that I did not want to answer the question itself. It is one of my mental bugs to consider “threshold prices” an idiocy, and to not support their survival.
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”, …
Thank you! The answer worked!