search

microsoft / advanced-formula-environment

Create, edit, and reuse formulas in Excel
https://aka.ms/get-afe
MIT License
113 stars 11 forks source link

Non-english array notation #44

Closed Hennsarv closed 1 year ago

Hennsarv commented 1 year ago

on non-english languages, where used comma for decimal point, the array notation is a bit different

english row-separator is , and column-separator is ; non-english row-separator is ; and column-separator is \

MIght be usefull when syntax validator recognish the difference. Actually the column-separated (horizontal) arrays are treated as error

jack-williams commented 1 year ago

Would you be able to provide us with a small example that illustrates the error?

We do have logic to detect the array separators in different languages, so if you are seeing an error then we may have a bug.

NWilson commented 1 year ago

@Hennsarv We would like to fix this if there is indeed a bug.

Please could you tell us:

If we know what language settings you have, we should be able to reproduce and fix the bug. At the moment however, I have tested AFE using Excel Desktop in Turkish (tr-TR) locale, which is one of the very few locales that have \ as their array-row-separator (only a very small number of locales re-use the arithmetic-division sign in this way!).

Hennsarv commented 1 year ago

Hi,

SOme explanation and the case

The issue raised when Peter Partholomew converted my Bridge table sample into Lambda formulas and sharing this in AFE format

[cid:af97bcac-6a5b-46d9-a558-8f3ac9a66134]

I liked to try those, installed AFE (i'd never used before) and using CopyPaste entered this as formulas (there is a small typo on line 35)

There is formulas in global region format (list separator , and column separator ;) My excel desktop run using et-ee region format (list separator ; and column separator )

Surprizingly copy-paste works fine and convert functions into correct format. The AFE user interface shows functions in global format but in name manager the are visible in local region format

Only problem ise that row-column list are switched (see below)

I made a simple test and result is visible below. This works only on desktop Excel. Online excel DONT support that kind of conversion

This is my test:

NB! Exel regional setting is ESTONIAN - list separator is ; column separator is \

// FIRST. This is function in local notation (language EE, list separator ; column seprataor ) =let(r;{1;2;3};c;{4\5\6};choose(x;r;c))

// this is how it after copy-paste into AFE =LET(r, {1; 2; 3}, c, {4\5\6}, CHOOSE(x, r, c))

// this is result in NAME MANAGER =LAMBDA(x;LET(r;{1;2;3};c;{4\5\6};CHOOSE(x;r;c)))

// SECOND. This is function in glbal (english) notation (list separator , column separator ;) // Excel still on EE-setting =let(r,{1,2,3},c,{4;5;6},choose(x,r,c))

// this is how it after cpy paste into AFE =LET(r, {1\2\3}, c, {4; 5; 6}, CHOOSE(x, r, c))

// this is result in NAME MANAGER =LAMBDA(x;LET(r; {1\2\3}; c; {4;5;6}; CHOOSE(x; r; c)))

[cid:85d25cc1-79e7-4b31-8e86-fb7a877ff02b]

Conclusion.

AFE is greate tool - I start to love it. Espesially functionality allowing entering (copy-paste) functions and formulas in both format - global and local. The ONLY issue is that array notation is flipped.

Thank You Henn Sarv

Saatja: Nicholas Wilson @.> Saadetud: reede, 14. aprill 2023 12:36 Adressaat: microsoft/advanced-formula-environment @.> Koopia: Hennsarv @.>; Mention @.> Teema: Re: [microsoft/advanced-formula-environment] Non-english array notation (Issue #44)

@Hennsarvhttps://github.com/Hennsarv We would like to fix this there is a bug.

Please could you tell us:

If we know what language settings you have, we should be able to reproduce and fix the bug. At the moment however, I have tested AFE using Excel Desktop in Turkish (tr-TR) locale, which is one of the very few locales that have \ as their array-row-separator (only a very small number of locales re-use the arithmetic-division sign in this way!).

— Reply to this email directly, view it on GitHubhttps://github.com/microsoft/advanced-formula-environment/issues/44#issuecomment-1508232417, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AFS5XOE4ZMUI4FTWPAG3YGLXBELA5ANCNFSM6AAAAAAWAHG5XU. You are receiving this because you were mentioned.Message ID: @.***>

Hennsarv commented 1 year ago

I see that pictures includes my post are lost. THere was 2 pictures - one showeing Peters functions and other showing the copypaste result.

Hennsarv commented 1 year ago

OK - I hope we can close this bug report - my fault. I will explain what is the reason of misunderstandings Becouse I typically don't use english format - I didn't know on small point

in local regional format we are using semicolon as list separator - parameters list in function is separated with semicolon in english (internal) regional format used comma as list separator

I call this default separator

I think that same separator logic is in array notation - default separator works differently, but

ROW separator is in BOTH notation semicolon (in my erroneous knowledge I think that default list separator is row but no) Array notation use his own logic and BOTH read ; as row separator

and column separator in array notation is second separator - in english it is comma and in our regional it is backslash

So conceptually Excel function parameters are in out local rows and in english format they are columns

Then mean in formula translation between local/internal not all semicolons are replaced with commas but only part of then

=HSTACK({ 1,0 ; 2,1 ; 3,2} ; {4 ; 5,5 ; 6}) // local format after translation =HSTACK({ 1.0 ; 2.1 ; 3.2} , {4 ; 5.5 ; 6}) // internal format

as we can see only one (middle) semicolon will be replaced with comma

NWilson commented 1 year ago

Thank you for that clarification. I have tested in Estonian, and I think AFE is working correctly as you describe.

I have discovered some small bugs though - there are erroneous "red underline" diagnostics used to indicate a syntax error, even though there isn't a syntax error with the \ (backslash) column separator. Our test cases actually cover Turkish, with its forward-slash column separator, but we don't currently have tests for your case of the backslash column separator, which isn't being highlighted correctly in AFE.

However, load/save seem to be fully functioning.

Thank you again for your report, we will be fixing any further issues you may find.