How to round values to the nearest 1000?

How to round values to the nearest 1000?

Like: 12,549,235 > 12,549,000
Like: 12,549,735 > 12,550,000

According to Help I should use =ROUND(P9,3-LEN(INT(P9))) but the result
is not what I want.

Bart
Excell 2003

Hi!

Try this:

=ROUND(A1/1000,0)*1000

Biff

"AA Arens" <bartvandongen@gmail.com> wrote in message
news:1152850880.524553.173510@75g2000cwc.googlegroups.com...
> How to round values to the nearest 1000?
>
> Like: 12,549,235 > 12,549,000
> Like: 12,549,735 > 12,550,000
>
> According to Help I should use =ROUND(P9,3-LEN(INT(P9))) but the result
> is not what I want.
>
> Bart
> Excell 2003
>

Or:

=ROUND(A1,-3)

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:OF11E7vpGHA.756@TK2MSFTNGP05.phx.gbl...
> Hi!
>
> Try this:
>
> =ROUND(A1/1000,0)*1000
>
> Biff
>
> "AA Arens" <bartvandongen@gmail.com> wrote in message
> news:1152850880.524553.173510@75g2000cwc.googlegroups.com...
>> How to round values to the nearest 1000?
>>
>> Like: 12,549,235 > 12,549,000
>> Like: 12,549,735 > 12,550,000
>>
>> According to Help I should use =ROUND(P9,3-LEN(INT(P9))) but the result
>> is not what I want.
>>
>> Bart
>> Excell 2003
>>
>
>

And one more if you have the analysis toolpak loaded:

=MROUND(A1,1000)

I'd use Biff's second answer, but =mround() may be interesting to review.

You may also want to look at =floor() and =ceiling() (just to know that they
exist).

AA Arens wrote:
>
> How to round values to the nearest 1000?
>
> Like: 12,549,235 > 12,549,000
> Like: 12,549,735 > 12,550,000
>
> According to Help I should use =ROUND(P9,3-LEN(INT(P9))) but the result
> is not what I want.
>
> Bart
> Excell 2003

--

Dave Peterson

Thanks all for your help. I choosed option 2.

Bart

Dave Peterson wrote:
> And one more if you have the analysis toolpak loaded:
>
> =MROUND(A1,1000)
>
> I'd use Biff's second answer, but =mround() may be interesting to review.
>
> You may also want to look at =floor() and =ceiling() (just to know that they
> exist).
>
> AA Arens wrote:
> >
> > How to round values to the nearest 1000?
> >
> > Like: 12,549,235 > 12,549,000
> > Like: 12,549,735 > 12,550,000
> >
> > According to Help I should use =ROUND(P9,3-LEN(INT(P9))) but the result
> > is not what I want.
> >
> > Bart
> > Excell 2003
>
> --
>
> Dave Peterson

>I'd use Biff's second answer

I'm so used to the first method that I overlook the "intended syntax". I
think the first method is more intuitive, though. (not taking into account
MROUND)

Biff

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44B7A2F8.C481032@verizonXSPAM.net...
> And one more if you have the analysis toolpak loaded:
>
> =MROUND(A1,1000)
>
> I'd use Biff's second answer, but =mround() may be interesting to review.
>
> You may also want to look at =floor() and =ceiling() (just to know that
> they
> exist).
>
> AA Arens wrote:
>>
>> How to round values to the nearest 1000?
>>
>> Like: 12,549,235 > 12,549,000
>> Like: 12,549,735 > 12,550,000
>>
>> According to Help I should use =ROUND(P9,3-LEN(INT(P9))) but the result
>> is not what I want.
>>
>> Bart
>> Excell 2003
>
> --
>
> Dave Peterson