how can i round in excel to the nearest thousand

[Guest]

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

 

[JE McGimpsey]

To just change the display:

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

=INT(OtherSheetName!A1/1000)

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]

OTOH, to actually round the value:

=ROUND(Sheet2!A1/1000,0)

 

[David Biddulph]

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)

 

[Rick Rothstein \(MVP - VB\)]

I need the number 1,802,224 to be pulled from anothe rworksheet

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)

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]

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

 

[JE McGimpsey]

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,

 

[Rick Rothstein \(MVP - VB\)]

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

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\)]

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