12 Replies Latest reply: Jan 18, 2013 6:33 AM by Badunit
alisonfrommerrylands Level 1 Level 1 (0 points)

I am using the sum function on numbers to add the value of a row of cells in a cashflow and each time the sum figure given is one over or under what my calculations are. I know for a fact my calculations are correct because the row consists of 4 lots of one number (1,167) and 8 lots of another number (933) this equates to two even amounts of an odd number. Now I'm no mathematical genius but if I multiply any number, be it odd or even, by an even number the result will always be that of an even number. So now pull out your calculator everyone and let's see… (4 X 1,167) + (8 X 933). Now let's explain it in layman's terms for those who are somehow able to design software programs even though they can't perform basic mathematical operations…4 X 1,167 = 4,668 and 8 X 933 = 7,464 (both even numbers, what a shock) now we add 4,668 and 7,464 together to get 12,132. My big question here is how the **** do the figures manage to add to 12,133 (an odd number, which is impossible in this case, as I hopefully explained quite cleary earlier) when I apply the sum function formula to collectively add the row of cells? 


MacBook Pro, OS X Mountain Lion (10.8.2)
  • 1. Re: Numbers won't add correctly
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Alison,

     

    It's an interesting argument, but testing your figures we don't see the problem This is an actual Numbers table with your figures in it, and I'm sure you could duplicate it.Screen Shot 2013-01-17 at 8.37.50 am.png

    I suspect that there are things you aren't telling us. You may not want to complicate the discussion with details, but they are critical to getting to the explanation.

     

    Jerry

  • 2. Re: Numbers won't add correctly
    Jeff Shenk Level 4 Level 4 (2,115 points)

    Alison,

     

    If you select the cells with the twelve numbers and adjust the format to show at least three decimal places, can you see why this might be happening?

     

    As Jerry said, Numbers does do this sum correctly.

     

    Jeff

  • 3. Re: Numbers won't add correctly
    Wayne Contello Level 6 Level 6 (13,620 points)

    please show your table as a screen shot and the formulas you are using.  Also...  did you type in the numbers:

    1167  and

    933

    Screen Shot 2013-01-17 at 7.59.31 AM.png

    or paste in from some place else where there may be hidden digits?

     

     

    also... I would confirm the formatting of the cells:

    Screen Shot 2013-01-17 at 8.00.32 AM.png

  • 4. Re: Numbers won't add correctly
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Alison,

     

    You said "4 lots of one number (1,167) and 8 lots of another number (933)"

     

    Here is a spreadsheet that uses SUM

     

    You said cashflow, so I used Currency format.

     

     

    $1,167.00

    $1,167.00

    $1,167.00

    $1,167.00

    $933.00

    $933.00

    $933.00

    $933.00

    $933.00

    $933.00

    $933.00

    $933.00

    $12,132.00

     

    The entries start in Column B and end in Column M. The formula in Column N on the right is:

     

    =SUM(B2:M2)

     

    Answer is $12,132.00, not "12,133 "

     

    Could there be some rounding errors in previous formulas that produce

    "4 lots of one number (1,167) and 8 lots of another number (933)" ?

     

    Ian.

  • 5. Re: Numbers won't add correctly
    alisonfrommerrylands Level 1 Level 1 (0 points)

    It's adding correctly to the 12,132 in the columns but not in the rows. This is where I'm really stumped. Decimal places are set to 0, as I'm only working with whole number through out the tables. Therefore the amount of decimal places should not matter. However, It still insists on being one up or one below in the rows.

  • 6. Re: Numbers won't add correctly
    Badunit Level 6 Level 6 (10,815 points)

    You might have to post a screenshot so we know what "adding correctly to the 12,132 in the columns but not in the rows" means. Obviously there is some difference in what is being added when you do rows vs columns.

     

    Setting the decimal places to 0 does not mean the value in the cell has nothing after the decimal.  That setting is for display of the number, it does not affect the actual number.

     

    If I have the number 123.45 in a cell and I format that cell to 0 decimal places it will look like 123 but if I use it in a formula, such as a SUM, it will use the number 123.45 in that formula.

     

    100.4 + 100.4 + 100.4 = 301.2

     

    if formatted with no decimal places it would look like this in your table:

     

    100 + 100 + 100 = 301

     

    So, if you follow the advice given previously and increase the number of decimal places to see what you really have in those cells, you might discover your problem.

  • 7. Re: Numbers won't add correctly
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Setting the format of the display to zero decimals only fools the eye. To set the value to zero decimal places, use either the ROUND or INT function.

     

    Jerry

  • 8. Re: Numbers won't add correctly
    alisonfrommerrylands Level 1 Level 1 (0 points)

    The numbers in all the cells of that particular row were typed in as whole numbers manually by myself. I can understand if the cels were a rounded figure then the sum total would add the original figures before rounding and round to the nearest whole figure at the end. I have created another table and it seems to be okay now so I will probably just start fresh with that one.

     

    Thanks for your help everyone.

     

    Alison.

  • 9. Re: Numbers won't add correctly
    alisonfrommerrylands Level 1 Level 1 (0 points)

    Oh thanks very much I will try that in future.

  • 10. Re: Numbers won't add correctly
    alisonfrommerrylands Level 1 Level 1 (0 points)

    That's where I'm lost for an explanation because the number in all the cells in the row were typed in as whole figures manually by me. As I said before if the figures were rounded amounts of a figure containing decimal places then I would understand it to sum up the original input values and round at the very end.

  • 11. Re: Numbers won't add correctly
    Wayne Contello Level 6 Level 6 (13,620 points)

    Alison,

     

    Please look in my profile and email me your document exhibiting the problem

     

    Thanks,

    Wayne

  • 12. Re: Numbers won't add correctly
    Badunit Level 6 Level 6 (10,815 points)

    I'm curious like Wayne is. Can you post the document or email it to me. I'd like to see this anomaly.