Re: [libreoffice-users] Question on value command and Err:502
At 14:48 24/11/2020 +1000, Michael D. Setzer II wrote: Put a more complex question, and got no responses yet. You have to admit that your description and layout were pretty foggy! Did anyone understand it? So, here it is in simplest form =value(1+1) converts the 1+1 to 2 But =value( ) fails with Err:502 if text "1+1" is used or if a cell containing the text 1+1 is used?? I've looked thru command, and this seems to be the one to convert a text string to a value? It is. But the text string "1+1" is not a value expressed as text but a numerical expression in text form. And VALUE() does not undertake to evaluate such expressions. Is there another command, ...; What you need instead is the EVALUATE() function. (PS: It doesn't exist!) I think the fact that VALUE(1+1) entered as part of a formula does what it does is a lucky side effect. Clearly in this case LibreOffice is doing something similar to the automatic editing that is done when any material is entered into a spreadsheet cell. I'm talking about this sort of thing: when you type "+02" (no quotes) into a cell formatted as Number, you see just 2. The "1+1" in this case is being entered into a cell (as part of a formula), not arising as the result of some calculation. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Question on value command and Err:502
You'd probably get more help if you made your emails understandable! Below is what I see from you ... (with some > quote marks on the left hand side) I can't even tell what you wrote in addition to what Brian wrote, let alone begin to interpret what you're asking. On Wed, 25 Nov 2020 00:02:04 +1000 "Michael D. Setzer II" wrote: > =On 24 Nov 2020 at 13:05, Brian Barker wrote: > > > Date sent:60;Tue, 24 Nov 2020 13:05:46 + > To:;=users@global.libreoffice.org > From:60;Brian Barker;b.m.bar...@btinternet.com > Subject:;Re: [libreoffice-users] Question on value command > and Err:502 > > > At 14:48 24/11/2020 +1000, Michael D. Setzer II wrote: > Put a more complex question, and got no responses yet. > > > You have to admit that your description and layout were pretty foggy! > Did anyone understand it? > > > So, here it is in simplest form > =value(1+1) converts the 1+1 to 2 > But =value( ) fails with Err:502 if text1+1is used or if a cell > containing the text 1+1 is used?? > I've looked thru command, and this seems to be the one to convert a > text string to a value? > > > It is. But the text string1+1is not a value expressed as text but > a numerical expression in text form. And VALUE() does not undertake > to evaluate such expressions. > > > Is there another command, ...; > > > What you need instead is the EVALUATE() function. (PS: It doesn't > exist!) > > > I think the fact that VALUE(1+1) entered as part of a formula does > what it does is a lucky side effect. Clearly in this case LibreOffice > is doing something similar to the automatic editing that is done when > any material is entered into a spreadsheet cell. I'm talking about > this sort of thing: when you type=+02(no quotes) into a cell > formatted as Number, you see just 2. The1+1in this case is being > entered into a cell (as part of a formula), not arising as the result > of some calculation. > > > As you mention, and as I found the evaluate command is what other > spreadsheets have that would seem to handle this, but libreoffice > calc doesn't?? > > > Have just been playing with that GPS to Decimal problem, and was > trying to get a simplier process to do it. > > > Have made a number of macros, that can do it just fine, but they have > long formulas. Was looking to do it with a set of regex commands. > Commands did what I wanted, but can't get it to calculate the result.. > > > Best I've gotten So Far. > Raw Data in column A > 0315'090E > Have a Macro that puts formula in column > B=REGEX(REGEX(REGEX(REGEX(REGEX(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),,+),',/60+ot;),CHAR(34),/3600),[EN],),[OSW],*(-1)) > That creates the Forumula 03+15/60+090/3600 > But value in macro fails if put around it, and using value on the > cell, or even a cell, where the data is copied fails. So, it is not > being seen as a formula. Seems Value only works on a string that only > contains digits and nothing else?? And as you said. LibreOffice > doesn't have evaluate as others do.So, macro does what I want, but no > way I've found to automatically get it to convert to the value. I can > do the following steps to get the result I want. After running the > macro I can manually do the following: F2,F9,CR,HOME,=,CR > That puts the cell in edit mode, then F9 calculates it to the text > formula and enter, the Home to go to the beginning, insert an = to > convert it from a text cell to an actual format, and then enter to > get the final result as digit values. > > > But again, LibreOffice doesn't have the keypress option to add that. > Other methods work fine, just was trying something different. > Just thought there would be some kind of command to do it.. > > > Additionally, it probable is a flux that value(1+1) worked, probable > does calculation first, and then sees nothing to convert. When it see > as string, it probable checks for non-digits, and thus fails > automatically.. > > > Thought there might be something else. Not an issue that effects me, > but thought it would be interesting to figure out.. > > > Have a nice day and be safe.. > > > > > I trust this helps. > > Brian Barker > > > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: > https://wiki.documentfoundation.org/Netiquette List archive: > https://listarchives.libreoffice.org/global/users/ Privacy Policy: > https://www.documentfoundation.org/privacy > > > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Question on value command and Err:502
=On 24 Nov 2020 at 13:05, Brian Barker wrote: Date sent:60;Tue, 24 Nov 2020 13:05:46 + To:;=users@global.libreoffice.org From:60;Brian Barker;b.m.bar...@btinternet.com Subject:;Re: [libreoffice-users] Question on value command and Err:502 At 14:48 24/11/2020 +1000, Michael D. Setzer II wrote: Put a more complex question, and got no responses yet. You have to admit that your description and layout were pretty foggy! Did anyone understand it? So, here it is in simplest form =value(1+1) converts the 1+1 to 2 But =value( ) fails with Err:502 if text1+1is used or if a cell containing the text 1+1 is used?? I've looked thru command, and this seems to be the one to convert a text string to a value? It is. But the text string1+1is not a value expressed as text but a numerical expression in text form. And VALUE() does not undertake to evaluate such expressions. Is there another command, ...; What you need instead is the EVALUATE() function. (PS: It doesn't exist!) I think the fact that VALUE(1+1) entered as part of a formula does what it does is a lucky side effect. Clearly in this case LibreOffice is doing something similar to the automatic editing that is done when any material is entered into a spreadsheet cell. I'm talking about this sort of thing: when you type=+02(no quotes) into a cell formatted as Number, you see just 2. The1+1in this case is being entered into a cell (as part of a formula), not arising as the result of some calculation. As you mention, and as I found the evaluate command is what other spreadsheets have that would seem to handle this, but libreoffice calc doesn't?? Have just been playing with that GPS to Decimal problem, and was trying to get a simplier process to do it. Have made a number of macros, that can do it just fine, but they have long formulas. Was looking to do it with a set of regex commands. Commands did what I wanted, but can't get it to calculate the result.. Best I've gotten So Far. Raw Data in column A 0315'090E Have a Macro that puts formula in column B=REGEX(REGEX(REGEX(REGEX(REGEX(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),,+),',/60+ot;),CHAR(34),/3600),[EN],),[OSW],*(-1)) That creates the Forumula 03+15/60+090/3600 But value in macro fails if put around it, and using value on the cell, or even a cell, where the data is copied fails. So, it is not being seen as a formula. Seems Value only works on a string that only contains digits and nothing else?? And as you said. LibreOffice doesn't have evaluate as others do.So, macro does what I want, but no way I've found to automatically get it to convert to the value. I can do the following steps to get the result I want. After running the macro I can manually do the following: F2,F9,CR,HOME,=,CR That puts the cell in edit mode, then F9 calculates it to the text formula and enter, the Home to go to the beginning, insert an = to convert it from a text cell to an actual format, and then enter to get the final result as digit values. But again, LibreOffice doesn't have the keypress option to add that. Other methods work fine, just was trying something different. Just thought there would be some kind of command to do it.. Additionally, it probable is a flux that value(1+1) worked, probable does calculation first, and then sees nothing to convert. When it see as string, it probable checks for non-digits, and thus fails automatically.. Thought there might be something else. Not an issue that effects me, but thought it would be interesting to figure out.. Have a nice day and be safe.. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
