Another option if you have set to display 2 decimal places is to use the
"precision as displayed" option, but be careful that it doesn't upset other
data, and you may want to switch the option back again after you've used it
and saved the new data.
--
David Biddulph

"jon" wrote in message
...
Thanks Mike,
I am aware of the Round function, but how would I apply it to thousands of
fields (without lots of manual work)?

Is there a way of rounding all fields that are selected like you can with
the format painter ?

So far, the only way I can find is to copy the woksheet, then delete all
the prices (so I titles and colour layout etc) then link field A1 to =
ROUND(SHEET1!A1,2) then drag this accross the worksheet.

But this is still time consuming when I have so many thousands of
worksheets to do.

Thanks


Jon


"Mike H" wrote in message
...
Hi,

Yes now I understand the question, try this

=ROUND(A1,2)

7.569585445 now becomes 7.57 and multiplying by 1000 = 7570

Mike



"jon" wrote:

Hi Mike,
Thanks for your reply

Formatting a field in Excel doesn't actually change the data that it
holds,
it just changes how it is displayed.

So for example, if field A1 holds a value 7.569585445 then formatting it
as
a number with 2 dp willl display as 7.57 but it will still really be
7.569585445 .

So if that value is the price for one widget, and in cell C1 I multiply
it
by 1000 to get the price for 1000 it will show as 7569.59 but it should
be
7570.

The products we sell are sold in the tens of thousands, so it can soon
show
significant differences in price.

Do you have any further suggestions ?

Jon





"Mike H" wrote in message
...
Jon,

but I now need to change all the fields to
actually be 2 decimal places, not just to display 2 decimal places.

And if a number doesn't have any decimal part what do you want to
display
.00 or something else?

If it's .oo then format as a number with 2 decimal places.

Mike


"jon" wrote:

Hi,
I have a price list that displays all prices to 2 decimal places, but
some
prices are actually to 8 or 9.

I have copied all the fields and done a Paste Special Values, which
has
removed the calculations, but I now need to change all the fields to
actually be 2 decimal places, not just to display 2 decimal places.

Is there a way of doing this ?

Thanks

Jon