3 Replies Latest reply: Jun 16, 2012 11:46 PM by Barry
Siddhanathaswami Level 1 (0 points)

I have a cell that is formated in currency (USD in this case, i.e. $1,000) and I want to add a calculation to that cell. I double click on the cell with the $1,000, put the cursor at the begining of the cell (before the $) and push "=". A forumula rounded box with a X and √ appears. I type in "+500". Now what shows is "=$1,000+500". I push return and get a syntax error! I now have to go back and delete the comma and the $. Yuck.


It's even worse, if you have a cell with $10 in it. Then the above process thinks it referencing row 10!


Excel handles this issue seamlessly.


Any ideas? Is this a bug?

Numbers, Mac OS X (10.7.4)
  • Barry Level 7 (29,939 points)

    Numbers is not MS Excel, and MS Excel is not Numbers. Each application has features that are not supported by the other.


    Not a bug, although it does appear to be a not-well-thought-out feature.


    The issue is that once the $ is in the entry box, the whole "$1,000" is perceived (within that cell) as a text string. Any formula using one of the arithmetic operators will choke on text.


    The second use of the $ sign is as the absolute reference operator. If it preceded a number equal to or smaller than the largest row number on the table, the combination will be interpreted in a formula as an absolute reference to that row, or to the cell in that row in the same column as the formula; whichever better fits the reference in that formula.


    Either way, you're not going to get the result you want, and you are likely to get a syntax error message.


    OTOH, in a cell where the fixed number has been entered as the result of an equation (eg. =1000), formatting the cell as currency will change the displayed value, but not the value as it appears in the entry box. Neither the $ sign nor the thousands separator (,) will appear, you will be able to append "+500" (without the quotes) to the end of the formula, and you will get the expected result ( $1,500) displayed in the cell. And, as the formula itself contains NO $, the issue of 10 being recognized as a row reference will not arise.


    For my own use, I'd simply avoid the currency format except for summary cells. A column containing a list of numerical values (with two places after the decimal) is easily recognized as a list of currency amounts, even without to $ sign. The same list with the sign applied to every value is cluttered, and more difficult to read.





    PS: If you want this feature changed in a future edition of Numbers, your best route is to Provide Numbers Feedback, using the menu item of that name in the Application menu (in Numbers, the "Numbers" menu). The link will also take you to the feedback page.

  • Siddhanathaswami Level 1 (0 points)

    Thanks Barry for the careful response. I've submitted feedback. I love Numbers and wish I didn't have to use Excel, but theres a few things like this that make me use both, all the time.


    Avoiding currency is probably the best solution here, but I also need to turn of comma separators for the thousands, which makes amounts very hard to read. Certainly not ideal!

  • Barry Level 7 (29,939 points)

    You can use the comma separators, provided you enter the values as a formula, do not include the separators in the formula, and format the cell to show the thousands separator, OR if you enter the value directly into the cell (including the separators, or with the separators added by formatting) and reference that cell in the formula (placed in a different cell) used to modify the value.