Apple Intelligence is now available on iPhone, iPad, and Mac!

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Currency format in Numbers 11.2

If I format a cell as Currency with 2 decimal points and I enter 14.19 I get $1419.00. Also, I can sometimes change a cell format from Automatic to (whatever) click out and cell format will not change.

Posted on Jan 12, 2022 2:23 PM

Reply
Question marked as Top-ranking reply

Posted on Jan 13, 2022 11:10 AM

The separators used for numbers and those used for currency are different in the first screenshot. The one for numbers has a comma for the decimal separator. It appears Numbers is using the ones for numbers, not currency. Change the separators for numbers so the "groupings" separator is a comma and the decimal is a point. I have no idea how they got that way for US-English. Currency and numbers should be the same.

13 replies
Question marked as Top-ranking reply

Jan 13, 2022 11:10 AM in response to bash_unix

The separators used for numbers and those used for currency are different in the first screenshot. The one for numbers has a comma for the decimal separator. It appears Numbers is using the ones for numbers, not currency. Change the separators for numbers so the "groupings" separator is a comma and the decimal is a point. I have no idea how they got that way for US-English. Currency and numbers should be the same.

Jan 12, 2022 3:57 PM in response to bash_unix

Assuming the result is actually 1419,00 not 1419.00, if you are someplace where the decimal separator is a comma, you need to use a comma. Here where we use the decimal point, if I type 14,19 I get $1419.00. I have no idea why it is that way versus being an error or being treated as text but it is. However, if your decimal separator is the decimal point and you are typing 14.19 and getting $1419.00, that would be a bug.


If you have a value in a cell THEN try to change the format, it will not stick if that value is not consistent with that format. It will ignore your attempt to change the format. I wish it would totally ignore it versus looking like it worked until you click away then back to the cell to see it did not work. A typical example is dates versus durations. Once it has been interpreted as one, you cannot change the format to turn it into the other.

Jan 12, 2022 5:03 PM in response to Badunit

However, if your decimal separator is the decimal point and you are typing 14.19 and getting $1419.00, that would be a bug.


The above is exactly what I'm entering 14.19 and the $1419.00 is what I'm getting I deleted the application, (Numbers) redownloaded it no change. I was unaware of the info in your second paragraph thank you.


Does anyone have any thoughts? I see others using the same formatting on uTube and they get $14.19?

Jan 12, 2022 6:04 PM in response to bash_unix

Tell us more. It is not happening here and this is the first time I have seen this problem mentioned here in over a decade of being on this forum so it may take some sleuthing to figure it out. Over the years there have been problems with other things for users in non-US regions but I have not seen this one.


In System Preferences/Language and Region, what is your region? At the bottom of that window click on Advanced and take a screenshot of the advanced settings or tell us what the grouping and decimal separators are for currency.

Jan 13, 2022 2:18 PM in response to Badunit

I noticed that difference when I did the screenshot. I didn't make the change until I heard back from you. Yes, that was the underlying cause, it now formatting correctly. However, the fact that the application is defaulting to the number separators and not the currency separators is in my opinion an application BUG.


Thanks for the assist.


Jan 14, 2022 2:25 PM in response to stfflspl

I see your point, so at a minimum, I'll call it unnecessary confusion. The fact that the cell formatting in Numbers allows for decimal point selection and thousand separators should override the system settings. I understand the logic here, use the System settings, and the developer is not required to code it within their application. I'll stick with [Unnecessary Confusion]


If the decimal point selection and thousand separators are selectable within Numbers it should override the system settings otherwise don't make selectable in Numbers.

Jan 14, 2022 4:31 PM in response to bash_unix

Currency format in Numbers does use the currency grouping settings in System Preferences, but when entering a number into the cell you have to use the correct decimal separator. When entering the value as number you use the decimal separator for numbers, when entering as currency you use the decimal separator for currency. That all seems to make sense, not a bug.


With my settings mismatched like the OPs with currency using a period and numbers using a comma, below are the results of different data entries.


In a currency formatted cell (with period as the decimal separator for currency):

14.19 become $1419.00 (strange result but it is what it is. It ignores the decimal as if it was not there)

14,19 displays as $14.19 (I entered it as a number with a comma, it displays as currency with a decimal as it should)

$14.19 stays as $14.19 (entered as currency using the currency decimal point)

$14,19 is text


In an automatic formatted cell (with comma as the decimal separator for numbers)

14.19 is text

14,19 stays as 14,19

$14.19 stays as $14.19

$14,19 is text


It is probably best to not mismatch the settings for currency vs numbers. It would be confusing. Is there anyplace in the world where they write currency differently than regular numbers?



Currency format in Numbers 11.2

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.