7 Replies Latest reply: Apr 21, 2013 9:51 AM by Barry
LavinaL Level 1 Level 1 (0 points)

I have calculated various percentages which give me more than two decimal places. I have kept the cells of these results at 2 decimal places. I have then used these various results and added them which have given me an error in the sum. I assume this is because the numbers I have added although seen as 2 decimal places have more than two decimal places. This means that my sum is various numbers off the right answer. How can I fix this problem?

 

Say 10.10 + 10.10 + 10.10 should SUM 30.30

 

But my answer comes to 30.29 as the 10.10 are not exactly 10.10 but as my cells are at 2 decimal places this is what I can see.

 

Hope I have explained myself.

 

 

Thanks for the help


MacBook Pro
  • 1. Re: decimal places on numbers
    Badunit Level 6 Level 6 (10,815 points)

    If you have formatted the cells to display with 2 decimal places, this did not affect the underlying numbers. The actual numbers still have all their decimal places, you just don't see them.  Use the ROUND function if you want them to actually have two decimal places. Then they'll add up to what you want.

  • 2. Re: decimal places on numbers
    LavinaL Level 1 Level 1 (0 points)

    where do i find the round function?

  • 3. Re: decimal places on numbers
    Barry Level 7 Level 7 (29,180 points)

    Actually, the addition gave you the 'right' result, or more accurately, a closer approximation of the correct sum.

     

    Badunit has given you the method to obtain the less accurate, but 'expected' result.

     

    You will find ROUND in the same place as you can find every function supported in Numbers—in the Function Browser. Click the Function button (ellow, and labeled with an ƒ ) and choose Show Function Browser.

     

    In the browser, type 'rou' or as much more of the name to bring the function name into view. Select it and click Insert to insert it into the cell containing the insertion point.

     

    Regards,

    Barry

  • 4. Re: decimal places on numbers
    LavinaL Level 1 Level 1 (0 points)

    Sorry i am new to numbers. Thank you for your help.

     

    I did what you mentioned and my cell gives me error with a red triangle when I use the ROUND function.

     

    It says "you must specify a value for all the required arguments".

     

    The cell I am rounding is a sum of two other cells. When I round the cell converts my SUM formula to

    = ROUND (num-to-round,digits).

  • 5. Re: decimal places on numbers
    Barry Level 7 Level 7 (29,180 points)

    Best advice I can give to someone new to numbers (or new to spreadsheets in general) is to download and read at least the first four chapters of the Numbers '09 User Guide.

    If you're going to write formulas, you'll also want to have the iWork Formulas and Functions User Guide, which lists and describes all the functions supported in numbers, and gives at least one example of using each in a table.

    Both guides are available for download via the Help menu in Numbers.

     

    Regarding your current issue:

     

    Put your SUM() formula in the space labeled 'num-to-round' and the number of decimal places you want to have in the result in the space labeled 'digits'.

     

    Here's and example:

    Picture 3.png

     

    The formula in D2 is shown above the table.

    The three other cells in the column calculate the same sum, but round it to the nearer tenth, whole number, and hundred respectively. Formulas listed below:

     

    C4: =ROUND(SUM(B),1)

     

    C6: =ROUND(SUM(B),0)

     

    C8: =ROUND(SUM(B),-2)

     

    Regards,

    Barry

  • 6. Re: decimal places on numbers
    Badunit Level 6 Level 6 (10,815 points)

    You need to use the ROUND function on the cells you are going to sum up. In your example, that would be the cells with the 10.10's in them.  You need them to actually be 10.10 before you sum them up, not 10.0967.  If you only round the sum, you will get the same result you are already getting, 30.29.

     

    Your formula will look like

    =ROUND(the formula that was in the cell before but without the equals sign, 2)

  • 7. Re: decimal places on numbers
    Barry Level 7 Level 7 (29,180 points)

    @ Badunit,

     

    Thanks for that correction. I was concentrating too closely on the syntax and lost sight of the details.

     

    Barry