I have this Excel file which is a price list subdivided in many tables
scattered around many worksheets according to product families. Some of the
worksheets have several price tables in them. Figures in price tables are
formatted so that visually one sees only two decimals, but in reality each
figure has many decimals behind it. (The number of decimals varies...)

How do I change things in the workbook so that every figure in every table
of every worksheet truly has only two decimals? This has become a problem
because employees use this workbook as a source of data via ad-hoc links to
other spreadsheets for their calculations and we always end up with numbers
off by a few cents since some use the function =round(number,2) while others
forget to use it, or round to a different number of decimals, when doing
calculations.

I think I can solve the problem by permanently setting each figure in the
source workbook to two decimals but I need a tip on how to do it
efficiently. Right now my choices are to manually go over each figure in
each price table (there are many price tables scattered around many
worksheets, and several worksheets have more than one price table in them!)
and retype it with only two decimals, or use the function =round(number,2)
in an unused portion of each worksheet, table by table, and then do a
Copy-Paste Special-Values and then cutting and pasting the result back in
the original table.

I am hoping that somebody comes up with a more efficient way of doing it
since the nature of the workbook and the different layouts of the price
tables would make all this very time-consuming...

Thanks!!
--
Tiziano

One method.

Set all numbers to show 2 decimal places then go to ToolsOptionsCalculation
and checkmark "precision as displayed"

Note this is Workbook option and will affect all sheets in that workbook.


Gord Dibben MS Excel MVP

On Sat, 7 Oct 2006 12:27:53 -0500, "Tiziano" wrote:

I have this Excel file which is a price list subdivided in many tables
scattered around many worksheets according to product families. Some of the
worksheets have several price tables in them. Figures in price tables are
formatted so that visually one sees only two decimals, but in reality each
figure has many decimals behind it. (The number of decimals varies...)

How do I change things in the workbook so that every figure in every table
of every worksheet truly has only two decimals? This has become a problem
because employees use this workbook as a source of data via ad-hoc links to
other spreadsheets for their calculations and we always end up with numbers
off by a few cents since some use the function =round(number,2) while others
forget to use it, or round to a different number of decimals, when doing
calculations.

I think I can solve the problem by permanently setting each figure in the
source workbook to two decimals but I need a tip on how to do it
efficiently. Right now my choices are to manually go over each figure in
each price table (there are many price tables scattered around many
worksheets, and several worksheets have more than one price table in them!)
and retype it with only two decimals, or use the function =round(number,2)
in an unused portion of each worksheet, table by table, and then do a
Copy-Paste Special-Values and then cutting and pasting the result back in
the original table.

I am hoping that somebody comes up with a more efficient way of doing it
since the nature of the workbook and the different layouts of the price
tables would make all this very time-consuming...

Thanks!!

Look at:

Tools==Options==Calculation==Precision as displayed

HTH


"Tiziano" wrote:

I have this Excel file which is a price list subdivided in many tables
scattered around many worksheets according to product families. Some of the
worksheets have several price tables in them. Figures in price tables are
formatted so that visually one sees only two decimals, but in reality each
figure has many decimals behind it. (The number of decimals varies...)

How do I change things in the workbook so that every figure in every table
of every worksheet truly has only two decimals? This has become a problem
because employees use this workbook as a source of data via ad-hoc links to
other spreadsheets for their calculations and we always end up with numbers
off by a few cents since some use the function =round(number,2) while others
forget to use it, or round to a different number of decimals, when doing
calculations.

I think I can solve the problem by permanently setting each figure in the
source workbook to two decimals but I need a tip on how to do it
efficiently. Right now my choices are to manually go over each figure in
each price table (there are many price tables scattered around many
worksheets, and several worksheets have more than one price table in them!)
and retype it with only two decimals, or use the function =round(number,2)
in an unused portion of each worksheet, table by table, and then do a
Copy-Paste Special-Values and then cutting and pasting the result back in
the original table.

I am hoping that somebody comes up with a more efficient way of doing it
since the nature of the workbook and the different layouts of the price
tables would make all this very time-consuming...

Thanks!!
--
Tiziano

Look at the ToolsOptionsCalculationPrecision as displayed option.

*DO* read HELP so you know what the consequences are.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tiziano" wrote in message ...
|I have this Excel file which is a price list subdivided in many tables
| scattered around many worksheets according to product families. Some of the
| worksheets have several price tables in them. Figures in price tables are
| formatted so that visually one sees only two decimals, but in reality each
| figure has many decimals behind it. (The number of decimals varies...)
|
| How do I change things in the workbook so that every figure in every table
| of every worksheet truly has only two decimals? This has become a problem
| because employees use this workbook as a source of data via ad-hoc links to
| other spreadsheets for their calculations and we always end up with numbers
| off by a few cents since some use the function =round(number,2) while others
| forget to use it, or round to a different number of decimals, when doing
| calculations.
|
| I think I can solve the problem by permanently setting each figure in the
| source workbook to two decimals but I need a tip on how to do it
| efficiently. Right now my choices are to manually go over each figure in
| each price table (there are many price tables scattered around many
| worksheets, and several worksheets have more than one price table in them!)
| and retype it with only two decimals, or use the function =round(number,2)
| in an unused portion of each worksheet, table by table, and then do a
| Copy-Paste Special-Values and then cutting and pasting the result back in
| the original table.
|
| I am hoping that somebody comes up with a more efficient way of doing it
| since the nature of the workbook and the different layouts of the price
| tables would make all this very time-consuming...
|
| Thanks!!
| --
| Tiziano
|
|