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

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.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ellemarr" <Ellemarr@discussions.microsoft.com> wrote in message
news:83439D51-49B5-4D39-8E9F-0860D3A7A96E@microsoft.com...
> 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

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





"Ellemarr" <Ellemarr@discussions.microsoft.com> wrote in message
news:83439D51-49B5-4D39-8E9F-0860D3A7A96E@microsoft.com...
> 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?

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






"Ellemarr" <Ellemarr@discussions.microsoft.com> wrote in message
news:83439D51-49B5-4D39-8E9F-0860D3A7A96E@microsoft.com...
> 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?

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?

"Ken Wright" wrote:

> 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.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Ellemarr" <Ellemarr@discussions.microsoft.com> wrote in message
> news:83439D51-49B5-4D39-8E9F-0860D3A7A96E@microsoft.com...
> > 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
>
>
>

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

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"ellemarr" <ellemarr@discussions.microsoft.com> wrote in message
news:C2CD14EA-49FB-46DC-AEB2-1DEAFB9BA9D2@microsoft.com...
> 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?
>
> "Ken Wright" wrote:
>
> > 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.
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
--
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
--
> >
> > "Ellemarr" <Ellemarr@discussions.microsoft.com> wrote in message
> > news:83439D51-49B5-4D39-8E9F-0860D3A7A96E@microsoft.com...
> > > 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
> >
> >
> >