Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  3. #3
    Uranium Lounger
    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

  4. #4
    3 Star Lounger
    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  6. #6
    3 Star Lounger
    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

  7. #7
    3 Star Lounger
    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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...

  9. #9
    Uranium Lounger
    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>=U11-SUM(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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •