how can i round in excel to the nearest thousand

J

Jamie

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

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.
Click to expand...

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

=INT(OtherSheetName!A1/1000)

???

Rick
 
H

Harlan Grove

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

=INT(OtherSheetName!A1/1000)
Click to expand...

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)
 
D

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)
 
R

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)
Click to expand...

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
 
J

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,
 
R

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>
Click to expand...

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

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

Rick
 
R

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
 
Top