Excel 2k rounding off issue… (Excel 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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).

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote

At a quick glance the fact that the percentage figures in D103:D107 only come to 99.99% could go along way towards explaining the variance

HTH

Peter

Reply | Quote

I see two things here.

1- The percentages in D103:D107 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:

=U11-SUM(D11:P11)

You would have to do something similar for the last calculation in all of the rows where the precentages are used.

Reply | Quote

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:

=ROUND(A1/B1,2)

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.

Reply | Quote