Skip navigation

Problem Adding Calculations to Currency Formated Cells

243 Views 3 Replies Latest reply: Jun 16, 2012 11:46 PM by Barry RSS
Siddhanathaswami Calculating status...
Currently Being Moderated
Jun 15, 2012 3:12 PM

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 Level 7 (29,095 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.

     

    Regards,

    Barry

     

    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.

  • Barry Level 7 Level 7 (29,095 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.

     

    Regards,

    Barry

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.