Created attachment 157787 [details] a minimal example: value-result pairs ordered and disordered I ran into a strange behaviour, when switch() gives "wrong data type" error on reordering value-result pairs. I've found out that the value-result pairs shall be ordered ascending in the switch function not to receive an error. Otherwise, if the value-results pairs are disordered, some values are not recognised and pairs with their result. It gives hard time to order the values and not to receive a #value error, but if it would be required, it shall be documented, too: file:///C:/Program%20Files/LibreOffice/help/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552 Personally, I think the behaviour is a bug. Version: 6.4.0.3 (x64) Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8 CPU threads: 8; OS: Windows 10.0 Build 18362; UI render: default; VCL: win; Locale: cs-CZ (cs_CZ); UI-Language: en-US Calc: threaded
Created attachment 157788 [details] WIS
reproducible with: Version: 5.4.7.2 (x64) Build-ID: c838ef25c16710f8838b1faec480ebba495259d0 CPU-Threads: 4; BS: Windows 6.19; UI-Render: Standard; Gebietsschema: de-DE (de_DE); Calc: single https://help.libreoffice.org/7.0/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552
It is not a matter of sort but data type. In E: =SWITCH(D3;"5";"a";1;"b";"2";"c";"3+4";"d") = b "5" it's a number like D3 =SWITCH(D3&"";"1*";"a";1;"b";"2";"c";"3+4";"d") = b D3&"" converts D3 in a text number. looks the issue is searching for numbers (expression) on text (value1)
OK, correct. Putting D3&"" for the expression works, independently on the format of the valueN "1" or 1, "2" or 2. Strikes me that =SWITCH(D3;"1*";"a";1;"b";2;"c";"3+4";"d") won't work then. That is comparing number with number does not work. But converting D to text numbers is OK. Not that in the original example, the value-result pairs are only reordered: SWITCH(A3;"1";"b";"2";"c";"1*";"a";"3+4";"d") SWITCH(D3;"1*";"a";"1";"b";"2";"c";"3+4";"d") But SWITCH(G3;1;"a";2;"b";"3";"c";4;"d") code decode 1 a 2 b 3 c 4 d blah #N/A works as a charm. That is the other way around: comparing text number "3" with number 3. So, how should one know, in which cases valueN is going to be interpreted as a text and in which cases as a number?
Hello community, I had found the same error and also created a sample document. In my example I always search for the number 1 in 3 value pairs. No problem in the first example. The three numbers are found. No matter what else I enter, the alternative value is displayed. In the second example I have no number in Value. If I now search for a number, an error is displayed instead of the alternative value. In the third and fourth example I mixed numbers and texts. If my value pairs start with a number, it will still be found. But once the pairs are interrupted by a text, the number is no longer found. All in all I suspect an error when searching for texts or numbers. If I search for numbers, I should also only get numbers as hits. You can adjust to this.
Created attachment 159477 [details] Example document to reproduce the bug
Created attachment 159478 [details] Modified sample file. Please rethink about what you are doing, F8: =SWITCH(D8;B8;C8;B9;C9;B10;C10;C11) searching for D8 in B column F8: =SWITCH(D8;C8;B8;C9;B9;C10;B10;C11) works fine for me.
Hello, everyone, I've been thinking very carefully. I have searched for the digit one in the column B (Value) in F8. Since there is no match here, the alternative value from cell C11 should actually be displayed. But it does not. We get a #VALUE error. The way Miguel changed the formulas, this is exactly the same as example 1, where I already mentioned that there are no problems with the first example. I don't understand the motivation why you changed my example. The function definitely does not work as expected. Best wishes and a happy Easter.
It seems type value is taken into account but it's not indicated in help: see https://help.libreoffice.org/7.0/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552 It could be interesting someone tests this on Excel. Indeed, https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr8.cxx?r=811b36e4#1919 ScInterpreter::ScSwitch_MS so I suppose it mimicks Excel. https://help.libreoffice.org/7.0/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552 IMHO, either it doesn't mimick Excel enough and code of "switch" should be changed, or help should be amended.
Created attachment 159558 [details] My example in xlsx from Excel Online This works as I am expected. Checked in Excel (free) Online.
Created attachment 159559 [details] Sceenshot from Excel Online For Information: The Switch function called in German in Excel : ERSTERWERT()
I gave a try with: https://gerrit.libreoffice.org/c/core/+/92345
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bb3ea0ae51bb943f7bb3dca80eee153f9c55c03e tdf#130583: fix Switch in Calc It will be available in 7.0.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Backports waiting for review here: - for 6.4 https://gerrit.libreoffice.org/c/core/+/92432 - for 6.3 https://gerrit.libreoffice.org/c/core/+/92433
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-4": https://git.libreoffice.org/core/commit/f075eaf3e6e4244cb7b92420e4286848bf22ed91 tdf#130583: fix Switch in Calc It will be available in 6.4.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-3": https://git.libreoffice.org/core/commit/c30b664f910ed980b4bf7c96f0ed23fe1d8be8e9 tdf#130583: fix Switch in Calc It will be available in 6.3.7. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I tested in daily Build 7.0.0.0 Perfect! Version: 7.0.0.0.alpha0+ (x64)Build-ID: 5063669a17538ea353e34ab0990363399d34f4f4CPU-Threads: 8; BS: Windows 10.0 Build 18363; UI-Render: Skia/Vulkan; VCL: win; Gebietsschema: de-DE (de_DE); UI-Sprache: de-DECalc: threaded
Then please Jürgen change the status from FIXED to VERIFIED.
Tested successfully with Build 6.4.4.0.0
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-3-6": https://git.libreoffice.org/core/commit/d8d1491b44f5e4522aaa81eea7dca2d320267614 tdf#130583: fix Switch in Calc It will be available in 6.3.6. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0960a8c5ce48adb1bc7bd434ed5242b51ce72149 tdf#130583: sc: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.