## [Solved] Rounding up to the nearest .05 or .09

### [Solved] Rounding up to the nearest .05 or .09

I have created a formula that mostly works, but would like to know if there is a better way to achieve rounding up to the nearest .05 or .09.

Here is the formula I came up with:

Code: Select all   Expand viewCollapse view
`=IF(ROUND(MOD(A1;0.1);2)<0.05;CEILING(A1;0.05);CEILING(A1;0.1)-0.01)`

There are two flaws with this formula I haven't worked out. The first occurs when the value of A1 has a zero in the 1/100th position (e.g. if A1=6.60, it will not round with my formula). The second occurs when there is a 5 in the 1/100th position (e.g. if A1=6.65, the formula will resolve to 6.69). I would prefer to leave the value unchanged in this case.

I appreciate any and all help!
Last edited by slyOO on Thu Dec 24, 2020 6:37 pm, edited 1 time in total.
Open Office 4.1.5
Windows 10 Pro
slyOO

Posts: 2
Joined: Thu Dec 24, 2020 12:08 am

### Re: Rounding up to the nearest .05 or .09

Welcome to the Forums.
While your topic seemed familiar, I was unable to find any other topic on the forum.
That said, the easiest way to handle your problem situations is to simply modify your current formula.
I'd like to suggest the following:
Code: Select all   Expand viewCollapse view
`=IF(VALUE(RIGHT(A1*100;1))/5;A1;IF(ROUND(MOD(A1;0.1);2)<0.05;CEILING(A1;0.05);CEILING(A1;0.1)-0.01))`

Long day so that's the best I can do now. I'll see if I can optimize it more in the morning.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6477
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Rounding up to the nearest .05 or .09

simpler variant
Code: Select all   Expand viewCollapse view
`=IF(MOD(INT(A1*100);10)>5; 0.09; 0.05)+INT(A1*10)/10`
LibreOffice 7.0.5.2 on Ubuntu 20.04.4 LTS

Posts: 32
Joined: Mon Jul 02, 2018 6:10 pm

### Re: Rounding up to the nearest .05 or .09

Thank you for the replies.

Code: Select all   Expand viewCollapse view
`=IF(MOD(INT(A1*100);10)>5; 0.09; 0.05)+INT(A1*10)/10`

This worked perfectly and remedied the two issues I was having with my formula. Thank you!
Open Office 4.1.5
Windows 10 Pro
slyOO

Posts: 2
Joined: Thu Dec 24, 2020 12:08 am