spreadsheets

  • Thread starter Thread starter jill
  • Start date Start date
J

jill

Using a microsoft works spreadsheet i find i cannot stop the answer to a
formula from rounding up or down eg .19 to 20. I asked this question before
but no-one could help intelligently except to correct grammar. I have sought
help for this before but I cannot remember the result as it was a while ago
and am now on a different computer
 
Well, near as I can remember you have to format the cell the formula is
in as number. Set the number of decimal places to the accuracy you require.

Hope that helps.

Later, Ray Parrish
 
jill said:
Using a microsoft works spreadsheet i find i cannot stop the answer to
a
formula from rounding up or down eg .19 to 20. I asked this question
before
but no-one could help intelligently except to correct grammar. I have
sought
help for this before but I cannot remember the result as it was a
while ago
and am now on a different computer
Click to expand...


The following reply applies to Excel 2003 and earlier. I can't speak
for Excel 2007.

There are three aspects to consider.

1. Default spreadsheet settings
Click on Tools | Customise | Edit.
You could tick the box marked "Fixed Decimal" and select a specific
number of decimal places. If you select this option, it will apply to
all cells in every spreadsheet you create.
OR you could untick the box marked "Fixed Decimal". (This is my
preferred option).

2. Formatting of individual cells
Select the cell. Then click on Format | Cells.
Click on the number tab and select the number category.
Enter the desired number of decimal places for that cell.

3. The formula
To illustrate this aspect, let us assume that the value of cell A1 is
100, the value of A2 is 2, and the value of cell A3 is 3.
In cell A4, enter the formula "=A1*A2/A3". Mathematically, this is the
same as two thirds of 100. Consequently, the correct answer will be
66.66666666 (recurring).

If you format cell A4 to display 0 decimal places, Excel will display
the result as 67. If you format cell A4 to show 2 decimal places, Excel
will display the result as 66.67. If you format cell A4 to show 4
decimal places, Excel will display the result as 66.6667.

However, the underlying value of cell A4 (which you cannot see) is still
66.666666 recurring.

If required, you can amend the formula in cell A4 to calculate the
result to a specified number of decimal places.

For example, if you change the formula to "=ROUND(A1*A2/A3,4)" or
"=ROUNDUP(A1*A2/A3,4)", Excel will calculate the result as 66.6667
exactly. if you change the formula to "=ROUNDDOWN(A1*A2/A3,4)", Excel
will calculate the result as 66.6666 exactly. However, the figure
displayed on the spreadsheet will still be governed by the default cell
format (or by any special format that applies to cell A4).
 
in addition to the suggestion
provided to you by others;

you can always ask questions
regarding office products and
works in either the main office
discussion group or even excel.

the ol'experts hang around the
link below and can provide
guidance:

http://www.microsoft.com/office/com...dg=microsoft.public.office.misc&lang=en&cr=US
--

db·´¯`·...¸><)))º>
DatabaseBen, Retired Professional
- Systems Analyst
- Database Developer
- Accountancy
- Veteran of the Armed Forces
 
Back
Top