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

Discuss the spreadsheet application

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

Postby slyOO » Thu Dec 24, 2020 1:13 am

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

Postby RusselB » Thu Dec 24, 2020 3:40 am

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.
User avatar
RusselB
Moderator
 
Posts: 6477
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby lader » Thu Dec 24, 2020 11:28 am

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
lader
 
Posts: 32
Joined: Mon Jul 02, 2018 6:10 pm

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

Postby slyOO » Thu Dec 24, 2020 6:36 pm

Thank you for the replies.

lader wrote:simpler variant
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


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 11 guests