How can I apply the ROUND function to a range of cells in a workbo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large worksheet full of formulas which all need to be rounded to the
nearest thousand. Without adding the "ROUND" command to every formula
individually (which would take forever) is there a way to apply rounding to a
whole range?
 
You can just use formatting to visually round what you are seeing, and if
you have other formulas that refer to that whole range, then you can adjust
those to round the data that they are getting from that range. Give us some
more detail and we can be a bit more specific.
 
If you don't ger a "real" answer, you can always fake it by cutting and
pasteing them to another sheet, and then use a ROUND formula to reference
them in place of where they were, like
=ROUND(Sheet2!A1,-3) and copy around...........

hth
Vaya con Dios,
Chuck, CABGx3
 
A "ROUND"-about way...........
First, do a Find&Replace to delete all your equal signs from the formulas in
the range you wish to change.
Then, well off to the side, put this formula in a same-row cell and copy
around to cover the range you have.
="@ROUND("&C4&",-3" This will give you a TEXT representation of the
formula you're after. Then, Copy > PasteSpecial > Values that TEXT
"formula" back to it's original location.........then just Find and Repalce
the @ with an equal sign........there you are............

Vaya con Dios,
Chuck, CABGx3
 
Through using format, the best I could do was get rid of the places to the
right of the decimal. Would formatting allow me to change $10,368,251 to
$10,369,000 without having to use the ROUND command?
 
Assuming you have formulas such as =$C$22*$D$1 in all your cells:-

BACKUP DATA FIRST.

In cell A1 put 1 and then in any other cell, put =$A$1
Copy that cell that contains =$A$1
Select your range of data and do Edit / Paste Special / Tick Formulas &
Multiply and hit OK. - Your cells will now all look like:-

=($C$22*$D$1)*($A$1)

Select all cells and do Edit / Replace, replacing = with %% - Formulas will
look like:-

%%($C$22*$D$1)*($A$1)

Select all cells and do Edit / Replace, replacing ~*($A$1) with ,-3) - Note
the tilde, and your formulas will now look like:-

%%($C$22*$D$1),-3)

Select all cells and do Edit / Replace, replacing %% with =ROUNDUP(

Done

Same principle for any other type of formula you want to add
 
Back
Top