Rounding a number to the nearest 5 or 9

[wyndam]

Hi all,

I am trying to work out a formula that will round a number to the nearest 5 or 9.

For example:

656 goes to 655
681 goes to 679
684 goes to 685
723 goes to 725
780 goes to 779
809 stays at 809
837 goes to 839
970 goes to 969

etc.

I have this formula currently: <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}</style>=MAX(INT(D2/10)*10 + IF(MOD(D2,10)>=5,9,-1),0)

It rounds to the nearest 9, but I need to get nearest 5 in there as well.

Any ideas or is this too many conditions to squeeze into one formula?

 

[Eric W]

Welcome to the board.

There may be shorter formulas, but this is pretty easy to follow:

=D2+CHOOSE(MOD(D2,10)+1,-1,-2,3,2,1,0,-1,2,1,0)

 

[wyndam]

Welcome to the board.

There may be shorter formulas, but this is pretty easy to follow:

=D2+CHOOSE(MOD(D2,10)+1,-1,-2,3,2,1,0,-1,2,1,0)

Thanks for the quick reply. I plugged this formula in and got these results:


656 goes to 655 correct
681 goes to 680 incorrect
684 goes to 686 incorrect
723 goes to 725 correct
780 stays at 780 incorrect
809 goes to 810 incorrect
837 goes to 839 correct
970 goes to 969 correct

Any thoughts on smoothing out those that are incorrect?

 

[JugglerJAF]

Hi Wyndam. Can you double check that the formula EricW provided is exactly what you're using?

It's working for me, returning the values you stated were required.
For example, I have 681 in D3 and the formula =D3+CHOOSE(MOD(D3,10)+1,-1,-2,3,2,1,0,-1,2,1,0) in E3 is returning a value of 679 for me, which is what you stated was required.

 

[wyndam]

Hi Wyndam. Can you double check that the formula EricW provided is exactly what you're using?

It's working for me, returning the values you stated were required.
For example, I have 681 in D3 and the formula =D3+CHOOSE(MOD(D3,10)+1,-1,-2,3,2,1,0,-1,2,1,0) in E3 is returning a value of 679 for me, which is what you stated was required.

I just noticed that the numbers I am using are actually rounded to the nearest whole number. So my 681 that I want to round to 679 (but is going to 680), is actually 680.83. I imagine this affects the formula. Is there a tweak to the formula I should do, or is there a way to treat a cell's rounded number as the actual number and not the original number with decimal places?

 

[Eric W]

Sure, you can round the value within the formula:

=ROUND(D2,0)+CHOOSE(MOD(ROUND(D2,0),10)+1,-1,-2,3,2,1,0,-1,2,1,0)

 

[wyndam]

Sure, you can round the value within the formula:

=ROUND(D2,0)+CHOOSE(MOD(ROUND(D2,0),10)+1,-1,-2,3,2,1,0,-1,2,1,0)

That works perfectly! Thanks so much!

 

[Eric W]

Glad we could help!