Results 1 to 9 of 9

20030416, 14:34 #1
 Join Date
 Dec 2001
 Location
 Atlanta, Georgia, USA
 Posts
 276
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel 2k rounding off issue... (Excel 2000)
is there a way in excel to have rounding off work right, or at least maybe better than it does? i have a spreadsheet that divides $$ figures by certain numbers and i end up with totals that are obviously different than what is needed. anyone know of a better way to do this or should i just put up with it?

20030416, 14:38 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
Can you be more specific? Complaining that "$$ figures" are divided by "certain numbers" giving "totals that are obviously different than what is needed" is not very useful in determining what is wrong.
Perhaps you can attach a small spreadsheet that illustrates your problem. It should be less than 100 KB (as .xls or zipped as .zip).

20030416, 15:02 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
There are a number of ways around this. We would need an example of what you are doing to tell you what the best solution would be for your situation. A couple of examples:
1 If the $$ are in A1, and the number you are dividing by is in B1, then you could use the following formula to do the divide and get an answer with two digits after the decimal:
<pre>=ROUND(A1/B1,2)
</pre>
A column like that should add up to the expected dollars and cents.
2 If you do not require extended precision in any calculations in the workbook, then you can select Options from the Tools menu. Click on the calculation tab, and then turn on the "Precision as displayed" option in the Workbook options section. Then if you format a cell to display dollars and cents, any calculation in that cell would be automatically rounded to two decimal places.Legare Coleman

20030416, 15:06 #4
 Join Date
 Dec 2001
 Location
 Atlanta, Georgia, USA
 Posts
 276
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
it's a spreadsheet that breaks down the telecom costs of a bill by division. there are 5 divisions and some costs are across the board. so when a single number is divided by 5 it might end up needing rounding. does that make sense? is there a way to fix this? for instance, the actual cost may be $1.80 but the divided total comes to $1.78. thanks for the help.
here's a copy of the basic file after some slight modifications.

20030416, 15:17 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
Sorry to keep on pestering you, but I can't discover any system in the way your last column (the one without formulas) relates to the one to the left to it. Would you mind explaining?

20030416, 15:22 #6
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
At a quick glance the fact that the percentage figures in D103107 only come to 99.99% could go along way towards explaining the variance
HTH
Peter

20030416, 15:25 #7
 Join Date
 Dec 2001
 Location
 Atlanta, Georgia, USA
 Posts
 276
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
wow you guys are fast!!! :)
i'm not certain what the last column has to do with anything, either, except that it is referenced in a ton of cells. basically, if a figure changes i change it in that last column and it is calculated for the row on which it is entered. so if the total again is 1.80 and i enter that into the last column then each cell with a formula works the formula using the cell in the last column. make sense?

20030416, 15:28 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
I think I'll exit before I make a fool of myself. Please try Legare's suggestions  he knows far more about Excel than I ever will...

20030416, 15:55 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2k rounding off issue... (Excel 2000)
I see two things here.
1 The percentages in D103107 add up to 99.99% not 100%. Therefore, you are going to have difficulty getting things to add up.
2 It looks like you can turn on the "Precision as displayed" option as described in my other post and it will solve any rounding problems without causing any other problems for this workbook. However, please test this in a copy of the workbook to make sure it does what you want,
However, even doing this is not going to solve the problem completely. If I turn on "Precision as displayed", and change the last precentage to 20.08 so that the precents add up to 100, things still don't add up exactly. For example, row 11 adds up to 7.01 when the original was 7.00. However, I get the same result if I do the calculation with my calculator and round all of the calculation to two digits after the decimal. You could get around that problem by changing the calculation in Q11 to:
<pre>=U11SUM(D11:P11)
</pre>
You would have to do something similar for the last calculation in all of the rows where the precentages are used.Legare Coleman