Skip navigation

Numbers won't add correctly

551 Views 12 Replies Latest reply: Jan 18, 2013 6:33 AM by Badunit RSS
alisonfrommerrylands Calculating status...
Currently Being Moderated
Jan 17, 2013 4:35 AM

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)
  • Jerrold Green1 Level 7 Level 7 (28,270 points)
    Currently Being Moderated
    Jan 17, 2013 5:40 AM (in response to alisonfrommerrylands)

    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

  • Jeff Shenk Level 4 Level 4 (2,005 points)
    Currently Being Moderated
    Jan 17, 2013 5:49 AM (in response to alisonfrommerrylands)

    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

  • Wayne Contello Level 6 Level 6 (12,790 points)
    Currently Being Moderated
    Jan 17, 2013 6:01 AM (in response to alisonfrommerrylands)

    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

  • Yellowbox Level 4 Level 4 (3,980 points)
    Currently Being Moderated
    Jan 17, 2013 6:13 AM (in response to alisonfrommerrylands)

    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.

  • Badunit Level 6 Level 6 (10,775 points)
    Currently Being Moderated
    Jan 17, 2013 10:59 AM (in response to alisonfrommerrylands)

    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.

  • Jerrold Green1 Level 7 Level 7 (28,270 points)
    Currently Being Moderated
    Jan 17, 2013 12:16 PM (in response to alisonfrommerrylands)

    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

  • Wayne Contello Level 6 Level 6 (12,790 points)
    Currently Being Moderated
    Jan 18, 2013 4:56 AM (in response to alisonfrommerrylands)

    Alison,

     

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

     

    Thanks,

    Wayne

  • Badunit Level 6 Level 6 (10,775 points)
    Currently Being Moderated
    Jan 18, 2013 6:33 AM (in response to alisonfrommerrylands)

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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.