Rounding a formula result to read thousands from millions.

[Guest]

I need to have the result read in thousands but use the number from millions.

Example, my formula result is 521,689,745.04 I need the cell to read
521,690. I need to take away more then just the two decimal places. I need
to take away the two decimal places plus the next three places and round up.

The column will start with "IN MILLONS"

The following does not accomplish what I want; =ROUND('[Monthy Report Prior
FY - ANG.xls]Sheet1'!$J$21,-3)

This will result in 521,690,000 and again I need 521,690.

Thank you in advance,
R Champ

 

[N Harkawat]

How about dividing it by 1000 as
=ROUND('[Monthy Report Prior FY - ANG.xls]Sheet1'!$J$21,-3) /1000

 

[Ken Wright]

One way that doesn't need formulas is to simply use a custom number format
and stick a comma on the end of an existing format, eg if the existing
format was say

#,##0

then addinga comma, eg

#,##0,

will suppress 3 digits in your values.

If you want to use formulas then you need to actually divide by 1000 in your
formula, eg

=ROUND('[Monthy Report Prior FY - ANG.xls]Sheet1'!$J$21/1000,0)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------