excel 2000 1

[andreas57]

i've got a table with a lot of diffrent formulas and i'd like to round numbers to 0.05 or 0.00. how can i do that in one step without having to put the formula in each cell?

thanks for the help andreas owen
aowen@arcade.ch

 

[Hasit]

I am sure you could do it with some code, but all you need to do is add an extra column. So if 0.05 is in column A1, then in column B1, type in:

ROUND(A1,0)

This will result in "0" in B1. Drag the cell downwards and it should populate column B with as many results as you have entries in column A.

Of course if you want 2 decimal places, then highlight column B, and format the cells (CTRL-1) to the desired format.

 

[NigelHarper]

Andreas,

This could be done with code but over the years I have learned that the quickest and simplest way, once you've done it a couple of time is the following.

Replace all the "=" with "#=" to turn the formulae into text and then create a copy of the sheet and in the copy of the table put the formulae "="=MROUND("&MID(OriginalSheet!CellREF,3,1000)&",0.05)into each cell where CellREF is the range of the corresponding cell in the original sheet and then copy the range and paste as values into the original sheet where they will appear as text. Finally, on the original sheet do a search and replace all "#=" with "=" and then another search and replace all "=" with "=" (yes that is right) and the formulae will be converted back into formulae. Then simply delete the copy sheet.
It sounds complicated but is actually very quick to do (literally less than two minutes, and is a useful cheat technique).
(This method using the "#=" and the MID function will get around any conditions in functions using the "=" sign.)

Hope this helps.

 

[DaleWatson123321]

andreas,

Hasit's option might be suitable, depending on whether you want the extra column(s) of formulas.

My understanding is that you want to modify your existing formulas to include the ROUND function - WITHOUT having to manually edit each and every formula.

If this is the case, then the following VBA code should be simple enough and flexible enough to make your task easy and fairly quick.

I have created two separate routines, which you should attach keyboard shortcuts to - for example attach <Control> Q to the routine for rounding to 2 decimal places, and <Control> W for rounding to zero decimal places.

The code includes a line which takes the cursor down to the next row each time you activate it, so it will be a matter of repeatedly using <Control> Q, for example, until you reach the end of the formulas which you want to add the ROUND function with 2 decimal places. Or, you can alternate from one to the other where necessary.

Here's the code...

Dim frm As String

Sub Rnd_2()
frm = ActiveCell.Formula
frm = Mid(frm, 2, 999)
frm = &quot;=Round(&quot; & frm & &quot;,2)&quot;
ActiveCell.Formula = frm
Selection.NumberFormat = &quot;0.00_);(0.00)&quot;
ActiveCell.Offset(1, 0).Select
End Sub

Sub Rnd_0()
frm = ActiveCell.Formula
frm = Mid(frm, 2, 999)
frm = &quot;=Round(&quot; & frm & &quot;,0)&quot;
ActiveCell.Formula = frm
Selection.NumberFormat = &quot;0.00_);(0.00)&quot;
ActiveCell.Offset(1, 0).Select
End Sub

Please advise as to how you make out, or whether you
require any further assistance.

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