Q: Numbers 3 has bugs in calculations.

Hi SG hi Gerd,

I can realy verify this Bug in my German iPad .

Using the summewenn(sumif ) function with German monthnames results in adding the consecutive pairs Feb/Mar , Apr/May, Jun/Jul, Sep/Okt, Nov/Dec together. Very strange.

Reiner

I get the miscalculation, too.

There seems to be a smartness-bug with months. Gerd, maybe you could confirm this:

Open a new file. In a cell type in Januar and hit tab, type in Februar and hit tab etc.

After entering Februar, Numbers changes it into März, which is March but not February and so on.

Edit: If you change the cell format to Text beforehand, it doesn't change automatically. But, using Gerd's file, it doesn't fix it if you apply "Text" as format on the existing tables.

So if it turns out to be a date smartness bug for German (probably other languages too because I remember a similar question involving month pop-ups) the folks at Apple would probably love to know the specifics so they can squash it:

Meanwhile, maybe try the workaround: Use Jerry's trick of typing the ' in front of the month name so that the value remains text. Do this in both tables.  Then see if the SUMIF works.

SG

Understood. Who knows if anyone's listening on the other side? Being very specific, though, can help. It does not seem to be a bug in calculations. It's a bug (we think) in how month names in non-English setups are being converted into dates.

Does the workaround using text values for months work?

SG

Hi Gerd,

What I meant was the month names typed as text with the apostrophe typed first so Numbers doesn't get too smart and convert them to dates. 

Instead of Januar type 'Januar, which will dispay the cell without the apostrophe.

That should work in any language.

SG

SG

Formatting the cell as text does not help.

A workaround in my table is to add a space behind the month names by hand and then the sumif gives correct results again.

Reiner

Reiner,

That's good to know that a space after the month names works. Not sure what that does "under the hood". But if it's working that's great!  Better than having to switch everything to English!

I, too, have found that just formatting cells as text doesn't make any difference. Numbers seems to insist on converting a month name to a date & time. 

But what does work here on my machine is Jerry's tip about typing an apostrophe first before the name of the month. The apostrophe (at least on the Mac) tells Numbers to treat what you type as text and not get too smart about guessing what you intend.

SG

SG, the workaround using 'Month works. However, I find it odd that Numbers actually converts text format into dates behind the scenes. I mean, in auto-mode that is totally fine, but when the user sets it to text explicitly, it should behave as such.

I wonder whether this is a bug or a feature. (You never know these days... )

I mean, in auto-mode that is totally fine, but when the user sets it to text explicitly, it should behave as such.

 

I wonder whether this is a bug or a feature. (You never know these days... )

Good point! It makes some sense for it to guess what you want if the format is set to Automatic. But not if you've set it explicitly to Text. Send feedback! 

And meanwhile, there appear to be at least two workarounds: Jerry's leading apostrophe and Reiner's space at the end.

SG

SG,

My last experiments on this issue today:

On the iPad the apostrophe thing does not work, may be because there is no straight apostrophe on the keybord.

The mismatch happens in sumif and countif, not in a direct comparision like. Cell A1 = Cell B1. and  there are pairs of month  like feb =mär = Februar = März. or November = Dezember. No Problems with Januar and August.

I hope someone from the numbers team reads here from time to time.

Reiner

Same Problem on a MacBook Pro with Numbers 3

https://discussions.apple.com/message/23577383#23577383

Is switching to English the only possible work arround?