How can you round a number to the nearest 900?

[trainer07]

what we are looking for is to round to the nearest 900. Example: if you have
$100,500 we want it rounded to $100,900 If we have $100,200 we want it
rounded to $99,900

Thanks!

 

[Mike H]

Try this

=MROUND(A1,1000)-100

Mike

 

[Bernard Liengme]

=ROUND(A30/900,0)*900
but not 100,500 rounds to 100,800 -- if must be a multiple or 900
best wishes

 

[Bernard Liengme]

Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes

 

[Bernard Liengme]

Typo: Not 100,900 but 100,800 !!!

 

[Mike H]

Surely no because that would round to a multiple of 900 so
100,500 rounds to 100,800 which isn't my understanding of what the OP asked
for

Mike

 

[Joel]

You need to play some tricks. Module arithmetic only works if you add 100 to
the number and then divide by 1000. Ignore my previous solution.

=IF(MOD((A1+100),1000)<=500,1000*(INT((A1+100)/1000)-1)+900,1000*(INT((A1+100)/1000))+900)

 

[Jim Thomlinson]

MRound only exists as a funtion if you have the analysis toolpak installed.
If it is not installed you get a #Value error. Instead of MRound you can just
use round something like this

round(A1, -3)
which is the same as
mround(A1, 1000)

Now you do not need the analysis toolpak
So the final formula would be...
=round(A1, -3) - 100

 

[Ed Cones]

Would you want 100,400 to round up to 100,900 or down to 99,900?

 

[Mike H]

Joel,

nice solution

Mike

You need to play some tricks. Module arithmetic only works if you add 100 to
the number and then divide by 1000. Ignore my previous solution.

=IF(MOD((A1+100),1000)<=500,1000*(INT((A1+100)/1000)-1)+900,1000*(INT((A1+100)/1000))+900)

 

[Mike H]

Jim,

Thanks for that but with the benefit of Joel's post I can see my solution
fails for 100401

Mike

 

[trainer07]

Hi, Mike!
Thanks so much! That worked out great. I have one more question. How
would I do the same thing, but round to the nearest 500?

 

[trainer07]

in regards to rounding to nearest 500, here are some examples...

$100,400 round to $100,500.....$100,800 round to 101,000.....100,749 round
to 100,500.....

 

[Mike H]

My 900 solution will fail for certain numbers, have a look at Joel's answer
but for a ((hopefully) less contreversial 500 try this

=MROUND(A1,500)

Mike

 

[Jim Thomlinson]

Something like this...

=ROUND(A1/500, 0) * 500
this avoids the use of the analysis toolpak. With the toolpak
=mround(A1, 500)

Where possible I avoid formulas that require the toolpak. If you send th
sheet to someone without the toolpak they can get #value errors...

 

[Dave Peterson]

The functions in the analysis toolpak have been moved into excel proper in
xl2007.

 

[Ed Cones]

Here's a version that seems to work. It rounds up from 400 and down from 399.

=(INT((G13-400)/1000)*1000)+900

 

[MrAcquire]

I think you're onto something, Ed! It appears that you can expand your
formula to calculate any value, v, to the nearest integer, n, between 100 &
999:

=(INT((v+500-n)/1000)+n

So to the nearest 900:
=INT((v+500-900)/1000)+900
or
=INT((v-400)/1000)+900 ...same as your post.

To the nearest 300:
=INT((v+500-300)/1000)+300
or
=INT((v+200)/1000)+300