ROUNDING All Cells In An Excel Worksheet 1

[palmese]

I have a number of spreadsheets with amounts hard-coded into the cells and denoted in millions.

Example:
Cell A1 = Cash Cell B1 = 1,000,000
Cell A2 = Fixed Assets Cell B2 = 3,500,000

Is there a way to round the numbers to the nearest thousandth (Cell B1 would be 1,000; Cell B2 would be 3,500) for the entire worksheet without undertaking the painstaking process of inserting the ROUND function in every cell? Essentially my problem is that I have hundreds of hard-coded worksheets with numbers and I need to convert the whole lot of them to the nearest thousandth.

Any ideas/thoughts would be most appreciated. Thanks.

 

[Zathras]

You can do this easily with VBA.

Keyboard: Press Alt-F11
Menu: Insert/Module

Copy and paste everything between the lines (see below) into the empty Module workspace.

Put the cursor somewhere between the first and last lines.

Keyboard: Press F5.
Menu: File/Remove Module1... (you don't want to run this twice!)

======================================================

Sub ScaleToThousands()
Dim c As Range
For Each c In ActiveSheet.UsedRange
  If Not c.Text = "" Then
    If Not c.HasFormula Then
      If c.PrefixCharacter = "" Then
        c.Value = Int((c.Value + 500) / 1000)
      End If
    End If
  End If
Next c
End Sub

=========================================================

 

[xlhelp]

Two non-vba solutions:

1. Go to Edit, Replace. in the find box type 000 then select "Replace All"

2. Type 1000 in a blank cell. Edit, Copy. Highlight cells to be converted. Go to Edit, Paste Special, Divide.
-Radix lecti

 

[Zathras]

A couple of points with the suggestions by xlhelp:

1(a). Any numbers with three zeros in a row will be affected in a way you may not want. E.g., 1,000,000 would become 1.

1(b). You example showed numbers all ending in 000. Is that really the case? If not, 1,000,123 would become 1,123.

2. Related to 1(b) -- If your numbers don't all end in 000, then the result would still carry the precision to the thousandths (.001). Not a problem as long as you are aware of it. It may in fact be preferable if you don't wan't to lose precision.

 

[DaleWatson123321]

Indu,

A bit of an "assist"...

Re option 1, it probably is not suitable because it requires that all values contain the "000" and this would likely not be the case - i.e. 1234567 / 1000 = 1235

Option 2 is a good option, but it can leave a bunch of cells with "0".

To prevent this, how about the following...

...after copying the cell containing 1000, hit the <GoTo> key <F5>, click &quot;Special&quot;, and choose &quot;Constants&quot;.

And then use: Edit - Paste Special - Divide.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlhelp]

zathras,

Good point. I automatically assumed everything was in millions. Wrong again.

Thanks. -Radix lecti

 

[palmese]

Many thanks to all who replied!!! Works great.

 

[xlhelp]

Thanks, Dale.

I glad there are people to catch mistakes, else it could cause more problems for the poster.

Thanks, again. -Radix lecti

 

[GlennUK]

I know a solution has already been found for this, but one easy way that wasn't mentioned was to alter the number format to display the figures scaled down by a factor of a thousand. You use a custom number format followed by a comma, for example ...
#,##0,
would factor displayed values down by a factor of a thousand ( double comma factors down by a million )

Glenn
Happy New Year to everyone.

 

[xlbo]

oooh - not seen that b4 Glen - def worthy of a star Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[DaleWatson123321]

Glenn,

Also thanks for the &quot;formatting&quot; option.

I believe it's important, however, to CAUTION readers - to note the &quot;key&quot; words in your description - &quot;would factor displayed values&quot;.

The &quot;display&quot; of a value does not change the actual value in the cell, and this does not fit with palmese's requirement of &quot;ROUNDING&quot; the actual values in their cells.

Just thought this needed a bit more clarification.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

That's cool Dale - I should've been clearer that I gave the star for the information NOT because it answered the question Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes