• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use of ";" key rather than "," in the formula

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I observed this thing just an hour ago while working at another branch office.
When I was entering any formula, it was not allowing to use "," key .

It was only when I use ";" key, the formula would work.
I am sure most of the Forum members must be aware of this.
But I faced this issue for the first time and I was :confused:

My doubt is, if someone has a formula as "=SUM(1;1)" in his file.
He sends me this file, my PC works on "=SUM(1,1)" combination.

Will this formula work at my PC or will it give me bug.
I could not mail the file with "=SUM(1;1)" to myself hence troubling the forum.

This is not urgent.

P.S. - The decimal in number formatting was displayed as ",".
Even few of the month names were with different spelling, (Okt, Jaan), can not recall correctly though :)
 
The formula if you will try to enter as it is in your office where you use "," as argument separator will definitely give error.

However, that will not affect you when you send file by email. Excel automatically adjusts those without you noticing them. So not an issue really. I am sure you must have downloaded SirJB's files few times. Did you get any issues with formulas there? :)

You just need to be careful when you post your solutions on international forums like these as the user if unaware of this and has different regional settings than yours may complain that the formula doesn't work although it works at your place :).
 
@shrivallabha , thanks a lot ! Yes, I never face issue with SirJB7's files. :)
I never knew that "Excel automatically adjusts those".

You have a very vaild point, "the user if unaware of this", this would have really confused me.

Good night.

P.S. - Got one example from SirJB7's post only. :)

=DIAS.LAB(D2;F2) -----> in english: =NETWORKDAYS(D2,F2)
=DIAS.LAB.INTL(D2;F2) -----> in english: =NETWORKDAYS.INTL(D2,F2)
 
Hi, sachinbizboy!

Even English is the most widely language used in Excel versions, other languages like Spanish are widely used too.

Formulas are affected by both language and regional settings values. So you can have USA English with the retrograde Imperial system and MDY dates, UK English with DMY dates, and every other language & regional settings local combination.

Formulas as you see them, not as Excel actually stores them internally, that it's in the English version. Then at open time, if you're using an English Excel version they're displayed in English, if a French one, in French, and so on, always mixing the conversion required by the language and the regional settings.

Well, not always. I just happened to discover something new at this thread:
http://chandoo.org/forum/threads/hyperlink-the-content-in-row-to-display-in-column.13593/#post-80281
In my Spanish Excel version the formula should be written as:
=CELDA("direccion")
which should be stored internally in English as:
=CELL("address")
Even the documentation and the built-in help say that "direccion" should be used, only now I realized that Excel shouldn't be so intelligent so as to make the conversion to "address" since it's a constant value.
So I left it in Spanish, selected the cell, went to the Immediate window and typed:
?activecell.formula
getting this:
=CELL("direccion")
Right as I thought but wrong in the concept. Then I said what if I write it in english? And typed this:
=CELDA("address")
Thinking it shouldn''t work, illogically it worked fine, repeated the VBA check and got as now expected:
=CELL("address")
So I left the sample file with the "address" even in Spanish so as to avoid a theoretical eventual manual conversion that should be done from "direccion" to "address", as I don't know and couldn't test if the interpretation of the Spanish word will work in English versions (the two way conversion or equivalence was not granted as I couldn't test it).

This is anecdotal but are things that are usually transparent and unnoticed for users who only use English Excel versions, or their own local ones but don't interact with people with other language versions.

Is I once wrote somewhere I place both my local and the English versions of the formulas for many reasons:
a) I wrote them in Spanish but these forums' language is English, so this last rules.
b) I sometimes write them manually (the short ones), other times with the immediate window, and other my one of my little monsters:
http://chandoo.org/forum/threads/excel-multilanguage-formula-translator-and-function-reference.4789/
c) Users come here for help with a problem, so why adding the additional problem of translating from a foreign language that they might not know?

Regards!
 
shrivallabha said:
Excel automatically adjusts those without you noticing them. So not an issue really. I am sure you must have downloaded SirJB's files few times. Did you get any issues with formulas there? :)
Click to expand...
Hi, shrivallabha!
So thought I, but not always, not always, check the previous post.
Regards!
 
Hi sachinbizboy,

Keep in mind that it's not always a simple swap of symbols. For example: {1/2/3/4/5} vs {1,2,3,4,5} OR IF(value;TRUE;FALSE) vs IF(value,TRUE,FALSE) between my colleague's version and mine.
 
SirJB7 said:
Hi, shrivallabha!
So thought I, but not always, not always, check the previous post.
Regards!
Click to expand...
Thank you. I always thought you'd be better person to explain this as you deal with it day in day out.

The only logical reason I can think is the part in Quote is a string constant [which is not enum-ed in the background] being passed and therefore ignored by the interpreter and possibly because it is from xlm functions. Maybe this extends to all such cases where we use string constants in xlm functions.
 
@shrivallabha
Hi!
It sounds to me like one more of the untidiness of Redmond guys, among them we can start with the DOS 640 Kb limit, the lack of a hard start button (so Ctrl-Alt-Del required), the buggy Excel function DATEDIF, the incapacity to accept animated .gif but in PowerPoint, the VBA implementation within Office suite (try to do something serious in Outlook or in Powerpoint and then get back to me), ... and the list goes on.
One day with a little of time maybe I'd dissect a .xlsx and .xlsm files to check how xml handles that, but I still wonder how did they manage to do that in .xls files. Who knows!
Regards!
 
SirJB7 said:
@shrivallabha
Hi!
It sounds to me like one more of the untidiness of Redmond guys, among them we can start with the DOS 640 Kb limit, the lack of a hard start button (so Ctrl-Alt-Del required), the buggy Excel function DATEDIF, the incapacity to accept animated .gif but in PowerPoint, the VBA implementation within Office suite (try to do something serious in Outlook or in Powerpoint and then get back to me), ... and the list goes on.
One day with a little of time maybe I'd dissect a .xlsx and .xlsm files to check how xml handles that, but I still wonder how did they manage to do that in .xls files. Who knows!
Regards!
Click to expand...
I did dissections (mostly on dead rats) in my 10+2 science (biology). That was all about. I took up engineering as I hated those dissections. I think we can keep it aside.

However, I'd say, if you can crank up one good article on this regional settings and formulas [especially ones like above, ";" argument separator and text / date functions which take beating] then it will be fantastic. Maybe I am little too enthusiastic. Hey r1c1 and Hui, what do you say?
 
@shrivallabha
Hi!
Do you know that until now I've been slippery enough to write an article, even tempted many times by Chandoo and Hui? And don't try to add your efforts to that subtle campaign! I promise to sneak surreptitiously before being squeezed enough. Remember my essential laziness. ;)
Regards!
 
You must log in or register to reply here.
Back
Top