how can i round in excel to the nearest thousand

Jamie

unread,

Jun 5, 2007, 4:25:03 PM6/5/07

to

I need the number 1,802,224 to be pulled from anothe rworksheet and be shown
as 1,802. as the result.

JE McGimpsey

unread,

Jun 5, 2007, 4:56:00 PM6/5/07

to

To just change the display:

Format/Cells/Number/Custom #,##0,

In article <EDF74AB9-E84B-4058...@microsoft.com>,

Rick Rothstein (MVP - VB)

unread,

Jun 5, 2007, 5:08:24 PM6/5/07

to

> I need the number 1,802,224 to be pulled from anothe rworksheet
> and be shown as 1,802. as the result.

Assuming that number is in cell A1 on the other sheet, this maybe....

=INT(OtherSheetName!A1/1000)

???

Rick

Harlan Grove

unread,

Jun 5, 2007, 5:35:00 PM6/5/07

to

"Rick Rothstein \(MVP - VB\)" wrote...

This doesn't round. It truncates to the nearest integer less than or
equal to the original value. If you're going to use a function, use
the correct one.

=ROUND(OtherSheetName!A1,-3)

JE McGimpsey

unread,

Jun 5, 2007, 5:36:14 PM6/5/07

to

OTOH, to actually round the value:

=ROUND(Sheet2!A1/1000,0)

In article <jemcgimpsey-86E6...@msnews.microsoft.com>,

David Biddulph

unread,

Jun 5, 2007, 5:54:10 PM6/5/07

to

But the OP wanted the result to be 1,802. , not 1,802,000
so it would have to be
=ROUND(OtherSheetName!A1,-3)/1000
or
=ROUND(OtherSheetName!A1/1000,0)
--
David Biddulph

"Harlan Grove" <hrl...@gmail.com> wrote in message
news:1181079300....@z28g2000prd.googlegroups.com...

Rick Rothstein (MVP - VB)

unread,

Jun 5, 2007, 5:54:49 PM6/5/07

to

Did you mean to write

=ROUND(OtherSheetName!A1,-3)/1000

It is unclear from the OP's original example whether he wants rounding or
truncation.

Rick

JE McGimpsey

unread,

Jun 5, 2007, 6:09:29 PM6/5/07

to

The subject line would tend to indicate "round"...<g>

In article <uYx0tw7p...@TK2MSFTNGP05.phx.gbl>,
"Rick Rothstein \(MVP - VB\)" <rickNOS...@NOSPAMcomcast.net>
wrote:

JE McGimpsey

unread,

Jun 5, 2007, 6:12:28 PM6/5/07

to

Actually, the OP was ambiguous as to whether the stored value should be
1,802 or whether it should just "be shown as 1,802".

If the latter, there's no need for functions at all:

=OtherSheetName!A1

Format as #,##0,

In article <Bqednb2sbKEaRvjb...@bt.com>,

Rick Rothstein (MVP - VB)

unread,

Jun 5, 2007, 6:15:37 PM6/5/07

to

>> It is unclear from the OP's original example whether he wants rounding or
>> truncation.
>

> The subject line would tend to indicate "round"...<g>

Oh sure, now we have to read the Subject lines too! <g>

Thanks, I missed that... I stand corrected.

Rick

Rick Rothstein (MVP - VB)

unread,

Jun 5, 2007, 6:30:22 PM6/5/07

to

> Format as #,##0,

As I have posted before, my main "world" is the compiled version of VB and
in my 25+ years of programming BASIC and VB, I never saw that 'end with a
comma" trick... I just tried it in the compiled version of VB and it works
in the Format statement there (one comma truncates/rounds off the thousands,
two trailing commas truncates/rounds off the millions, and so on). Very
interesting. After all these years... still learning new things about an old
friend. Thanks for posting/emphasizing it.

By the way, that means there is another alternative for the OP's question...

=--TEXT(OtherSheetName!A1,"#,###,")

as a number (leave off the double minus signs for text).

Rick