6 Replies Latest reply: Mar 26, 2010 10:11 AM by KOENIG Yvan
SKYTORT Level 1 (0 points)

I have recently finished my spreadsheet for personal finance tracking, and have just encountered what may be a bug in numbers.

I have a few sheets in which i collect the relevant data to be used by the appropriate functions.

This is what I have noticed.

In one sheet I have spending categories in column 1, and the relevant months in the top row. I have used the SUMIFS function to total transactions by category and month. I wrote the first row of functions and when I was done I merely copied and pasted the functions into the adjacent rows since they are exactly the same, the only variable being the spending category, which I allowed for.

I have about 10 categories and they all work perfect except for two rows. I find this very unusual since I have copied and pasted the functions, so no room for error, and not only that, all the other rows work perfect - just these two rows don't do the job. Also, it does not give the error sign (the red triangle with the exclamation mark), but the cells simply return the value $0.00. I even re-wrote the functions to try a different method other than and copied version of the function, but I can't get the values to display correctly in the cells.

Anyways, I just thought that if there is someone else that is encountering the same problem it would be nice to know.



Macbook Pro, Mac OS X (10.5.8)
  • Level 8 (41,790 points)
    My guess is that the error is in your document, not in Numbers.
    If you get 0.00 and not a red triangle, it's that your formula grabs datas from existing empty cells.

    You may send the document to my mailbox if you wish that I try to find the error.

    If datas are 'confidential', you may replace them by fake ones.

    Click my blue name to get my address.

    Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 17:44:13
  • Jerrold Green1 Level 7 (29,960 points)

    If you post a screen shot of your table and give us the expression you are using in the calculations that are failing, we may be able to figure it out.

  • Badunit Level 6 (11,615 points)
    Without seeing it, it's hard to tell what's going wrong. Two possibilities are

    1) The numbers to be summed are in cells formatted as text. Text, even if it is a numeral, will not sum.

    2) One or more of the test conditions is not exactly the same as what is in the table. Case doesn't matter but maybe there is a trailing space or an extra space somewhere in the word in your table or in your formula.
  • SKYTORT Level 1 (0 points)
    Hi All,

    This is the response I received from KOENIG a few minutes ago via a private email (Because I can't post screens shots on here due to the fact that I have no public sharing capabilities. On the other hand, screen shots would not have help since one had to physically open the file to pick up the mistake I made). Obviously not a bug in numbers, but extra spaces. I quote KOENIG:

    "Simple response.

    In your popup menu, the menu item is not "Clothing" but "Clothing ". The extraneous space made the difference.

    For Gas, it's the opposite.

    The popup menu item is correctly "Gas" but the cell A27 of Overview ;; Table 1 contains "Gas " . The extraneous space made the difference.

    As I'm very busy I ask you to post the response in the forum so that other helpers will not continue to scrap their head trying to understand.

    Yvan KOENIG (VALLAURIS, France) vendredi 26 mars 2010 11:16:05"
  • Jerrold Green1 Level 7 (29,960 points)

    Since I am prone to this same error, I always use Copy/Paste when setting up such comparisons.

  • Level 8 (41,790 points)

    At last I have responded to the 50 mails received and finished what I had to do in the garden.

    I wish to add that when something doesn't behave as we wish, 99.99% of times, it's because we made a typo.
    Here is a list of common ones :
    (1) Extraneous spaces
    (2) Unwanted duplicated chars
    (3) unwanted or missing parenthesis (or double quotes)
    (4) Error with the parameters separator (semi-colon versus comma)
    (5) add you own common ones

    Case 3 is quite easy to identify because most of the times, it generates a syntax error.
    Case 4 is easier to identify because, as far as I know, it always generates a syntax error.
    I apologizes but I can't describe Case 5's behaviour
    Case 1 and 2 are the more annoying because they may give a formula returning a wrong value without any kind of error message.

    It may be a good idea to keep this list near your machine.

    Yvan KOENIG (VALLAURIS, France) vendredi 26 mars 2010 18:09:36