Trying to get my 2021 Taxes done today, but struggling with Numbers. (please excuse the inefficient screengrabs)
I have a column of numbers which I formatted as "Currency" (US Dollar (US$):
...and inserted this Formula to Sum them, but get a Sum of "0 US$":
I have gotten this to work, before, but not today, when I'm in a bind to get'er done before nightfall.
Any help would be greatly appreciated, and kind regards from Berlin!
The left alignment of the "numbers" in the second image suggests these are being interpreted as "text" rather than numbers.
That would also be consistent with the ""o Us$" result.
Check the data format setting for the cells in D161:D327. It should be set either to Number or to Currency.
Regards,
Barry
The left alignment of the "numbers" in the second image suggests these are being interpreted as "text" rather than numbers.
That would also be consistent with the ""o Us$" result.
Check the data format setting for the cells in D161:D327. It should be set either to Number or to Currency.
Regards,
Barry
Is the complete column D formatted as Currency?
Please change the number of Decimals to 1 and see if all entries will change.
Only if they will change they are real numbers, if they don't change it is a text (that looks like a number)
Your values are all left aligned, numbers are normally right aligned.
Did you enter these values manually or did you copy them from somewhere?
If you copied them it could be text.
Ralf
Ah! I think I found the culprit, after finding this forum post:
Can't change cell number format - Apple Community
Since the csv I imported uses a period as decimal separator, and Numbers expects a comma, it will always interpret the values as Text...as soon as I changed on of those numbers to use a comma, instead, it jumped to right-aligned.
The question now is: how can I replace all those periods with commas, since I spent hours pruning this spreadsheet.
You might try making a COPY of your document, then changing the REGION of the copy to one that uses the period as the decimal separator, and the comma as a thousands separator.
OR, again working with a COPY of your document you could use" Find-Replace with" to replace the periods in the document with commas. If there are commas in some of the cells containing text, You could copy the numerical columns, paste them into a separate document, do the Find-Replace, then copy the converted numbers and past them back into the (copy of the) document they came from.
Regards,
Barry
PS: Find-Replace is activated in Numbers by pressing command-F.
Note that its search is global within the document, which is why I've suggested working with a COPY to make the changes (and moving the values to be changed to a second time COPY of the column(s) involved.
B.
Thanks for the update.
I'm still looking at the left alignment of the values in the selected cells of column D. According to the Cell format (which was readable in the original screen shot) the data format has been set to Number, a value type that defaults to alignment to the right side of the cell. Please click the Text button to the right of Cell in the format inspector, and confirm that the left-right alinement setting is Automatic, as shown here:
If the setting is "A", and the values are aligned left, they are definitely being recognized as text.
Ralf-F's suggested test—selecting all cells in column D, then changing the Decimal setting to 1 (or to 3) and observing the change is what is displayed in the column—is also a quick and valid test.
Regards,
Barry
Thanks for trying, Barry, but that's why I included the big-a** screengrab, to show that those cells were already set to currency.
I just tried again, but no luck:
So strange, as I used this method for years to itemize my deductions, and never had this problem before.
PS: sorry, I just realized that images embedded in replies don't open to reveal more detail, when clicked on...so here is the detail that is all but impossible to make out in the screengrabs above:
I imported this whole table from a csv file that I downloaded from my bank.
They were left-aligned manually, when I tested various settings...I just right-aligned them again, manually.
You seem to be correct that they are being interpreted as text, as their decimals don't change when I change them in the Data Format panel.
Even if I select a single field and change it to Number or Currency, it still reacts like Text.
How can cells be forced to be formatted as currency or numbers, when what I do doesn't appear to have any effect?
There seems to be something inherently different when importing from a csv file, than when I build the tables and numbers from scratch, I just can't figure out why that is...it would seem that importing csv's for further processing is a rather common use-case scenario for Numbers, no?
PS: I also just noticed that after I change focus with my cursor, the cells jump back to the "Automatic" Data Format.
I did that, and you are both correct: the values are being interpreted as Text, even if I set them to Number or Currency.
The question is: why can't I change that, or **how** can I override that?
I can't seem to find a "Search and Replace" type of function in Numbers, and changing over 350 values by hand is not something I want to spend my time with.
Anyone's got any ideas/solution?
Found it!...it's in the "View" menu on the very left of the toolbar.
Let's try to do this!
And BOOM, it works :-D
Thanks for helping me figure this out, guys!
You're welcome, till.
Thanks for the detective work that revealed the culprit!
Regards,
Barry
Would one of you mind marking the "Best Answer", so that others can quickly get to it?
Thanks in advance and have a good night, y'all!
Apple Numbers: Summing of Column not working for me
