How can you round a number to the nearest 900?

  • Thread starter Thread starter trainer07
  • Start date Start date
T

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!
 
=ROUND(A30/900,0)*900
but not 100,500 rounds to 100,800 -- if must be a multiple or 900
best wishes
 
Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes
 
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
 
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)
 
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
 
Joel,

nice solution

Mike

Joel said:
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)
Click to expand...
 
Jim,

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

Mike
 
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?
 
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.....
 
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
 
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...
 
The functions in the analysis toolpak have been moved into excel proper in
xl2007.
 
Here's a version that seems to work. It rounds up from 400 and down from 399.

=(INT((G13-400)/1000)*1000)+900
 
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
 
Back
Top