How can you round a number to the nearest 900?
 

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!

How can you round a number to the nearest 900?
 

Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

=ROUND(A30/900,0)*900
but not 100,500 rounds to 100,800 -- if must be a multiple or 900
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"trainer07" wrote in message
...
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!

How can you round a number to the nearest 900?
 

Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mike H" wrote in message
...
Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
=ROUND(A30/900,0)*900
but not 100,500 rounds to 100,800 -- if must be a multiple or 900
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"trainer07" wrote in message
...
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!

How can you round a number to the nearest 900?
 

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


"Bernard Liengme" wrote:

Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mike H" wrote in message
...
Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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)

"trainer07" wrote:

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!

How can you round a number to the nearest 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
--
HTH...

Jim Thomlinson


"Mike H" wrote:

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


"Bernard Liengme" wrote:

Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mike H" wrote in message
...
Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

Joel,

nice solution

Mike

"Joel" wrote:

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)

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

Jim,

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

Mike

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Mike H" wrote:

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


"Bernard Liengme" wrote:

Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mike H" wrote in message
...
Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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?


"Mike H" wrote:

Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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" wrote:

Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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

"trainer07" wrote:

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?


"Mike H" wrote:

Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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...
--
HTH...

Jim Thomlinson


"trainer07" wrote:

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" wrote:

Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!

How can you round a number to the nearest 900?
 

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



Jim Thomlinson wrote:

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
--
HTH...

Jim Thomlinson

"Mike H" wrote:

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


"Bernard Liengme" wrote:

Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mike H" wrote in message
...
Try this

=MROUND(A1,1000)-100

Mike

"trainer07" wrote:

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!




--

Dave Peterson

How can you round a number to the nearest 900?
 

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

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

"trainer07" wrote:

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!

How can you round a number to the nearest 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



"Ed Cones" wrote:

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

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

"trainer07" wrote:

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!