Question on value comand and Err:502

users
#1

Put a more complex question, and got no responses yet.
So, here it is in simplest form
=value(1+1) converts the 1+1 to 2
But =value( ) fails with Err:502 is text "1+1" is used or i 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? Is there another command, or am I doing something
wrong?

Contents of a simple spreadsheet

2 "=value(1+1)
Err:502 "=VALUE("1+1")
"1+1 TRUE =ISTEXT(A3)
Err:502 "=value(a3)

#2

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

#3

=On 24 Nov 2020 at 13:05, Brian Barker wrote:

Date sent:60;Tue, 24 Nov 2020 13:05:46 +0000

#4

=Wanted to see if it could be done outside libreoffice calc, since it doesn't
want to work.

Since I don't have the original user info, I created a file with 1000
randomly created data elements.

Created a bash script using sed to do the same processing.
Shell script gps3.sh

sed -e 's/^/=(/;s/\/+/;s/\x27/\/60+/;s/\x22/\/3600/;s/O\|S/\)*(-1)/;s/N\|E/)/'gps.txtgps.csv
sed -e 's/^/(/;s/\/+/;s/\x27/\/60+/;s/\x22/\/3600/;s/O\|S/\)*(-1)/;s/N\|E/)/'gps.txtgps.raw
paste -d, gps.txt gps.csvgpsboth.csv</div>bc -lgps.rawgps2.txt
paste -d, gps.txt gps2.txtgpsbc.csv#libreoffice --infilter=CSV:44,34,76,1 gps.csv gpsboth.csv gpsbc.csv

To process the 1000 records time ./gps3.sh reports.

real0m0.037s
user0m0.019s
sys0m0.009s

So, less than 4 hundredth of a secondFirst sed line creates a file that just has the decimal version for matching line
Second sed line makes a version of the output for use as input to bc, since it doesn't like the leading
= that is needed to import into libreoffice.
paste command just makes a combined version of input data, and result in columns A and B
bc -l takes the formula file, and actual calculates the final number, so no need for libreoffice to
calculate it.
paste again combines the two files.
For time test the loading to libreoffice is commented out.

Did find an error in my earlier process thou..
In other macros, date is converted to time value so the multiply by -1 for values didn't need ( ).
The above adds that..

Well, a simple solution that works, and libreoffice can read the csv files without issue..
Also, a lot faster...

Again. Have a nice day.

#5

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.

#6

Haven't found documentation to confirm but I believe your first example
=value(1+1) is recognized as a formula, the formula is processed and the
result converted to a value.

It works for me if I enter =value(sum(1,1)) the cell displays 2 and
=CELL("type",<address>), pointed at that cell, displays v

If I format a cell as text, enter a decimal digit, e.g. 2, and point
value() at that cell it returns the number 2.

If i point cell("type",<address>) at the cell containing digits that are
formatted as text the result is "l" (lower case L).

If I point cell("type",<address>) at the value() formula that points at the
text formatted digits it returns v.

So, I would say that value() converts a number formatted as text to a
value, converts a formula that results in a number to a value, but will not
recognize a formula written as a text string and convert the string to
produce the value.