New behavior in Calc for OOo 3.2: Calculating with text

[acknak]

Since 3.2 is now released, it's likely that there will be more questions like this one: http://user.services.openoffice.org/en/ ... =9&t=27444 due to the changes in the way Calc handles conversion of text to numeric values.

In some situations, a calculation may require that a quantity represented by some text be converted to a numeric quantity. The rules regarding such conversions have changed. These changes only affect formulas that you create; nothing has changed as far as entering dates or any other kind of data.

Just for kicks, I put together a small sample sheet for testing conversions in OOo Calc 3.1.1 and 3.2 (and Excel 2003).
Red/pink-highlighted cells show text-to-number conversions that behave differently in 3.2 compared to 3.1.1 and earlier; yellow cells show new behavior that's different from previous versions but now compatible with Excel.

The top section looks at using text strings in formulas; the lower section shows calculations done on text values in cells.

The good news is that Calc is now more consistent, more compatible, and more sensible:
• Calc now follows the same rules for numeric text, whether the text is in a formula or in a cell.
-- Before, Calc would convert text in a formula but not in a cell.
• The new behavior is more like Excel and should allow more sheets coming from Excel to "just work".
-- Calc will now use numeric text like Excel, although Calc still rejects many text values that Excel accepts.
• If automatic conversion is rejected, Calc now generates an error
-- Before, Calc always converted text to zero, so problems were more difficult to detect.

The bad news is that these changes will break some existing Calc spreadsheets that use numeric text in formulas. Where Calc would previously accept them, now, in most cases, they will fail with an error.

The error can be fixed by modifying the formula:
• Wrapping the numeric text with the VALUE() function to do an explicit conversion is equivalent to the old behavior in most cases.
• Changing the text to use an accepted standard format--if one is available.
• Moving away from using a numeric text at all. This is the best choice in terms of speed and portability, avoidng the text to number conversion altogether. E.g. instead of "2/16/2010", write DATE(2010;2;16).

[vasa1]

.....

The bad news is that these changes will break some existing Calc spreadsheets that use numeric text in formulas. Where Calc would previously accept them, now, in most cases, they will fail with an error.

The error can be fixed by modifying the formula:
• Wrapping the numeric text with the VALUE() function to do an explicit conversion is equivalent to the old behavior in most cases.
• Changing the text to use an accepted standard format--if one is available.
• Moving away from using a numeric text at all. This is the best choice in terms of speed and portability, avoidng the text to number conversion altogether. E.g. instead of "2/16/2010", write DATE(2010;2;16).

Hi acknak,

I'm a very basic user of Calc so I didn't follow your post but could you please explain again the part in bold? Are you suggesting that users now type out DATE[2010;2;16] instead of 16/2 (to get the current year added on automatically)? When I type DATE(2010;2;16) what I see after hitting enter is exactly that: DATE(2010;2;16)!

Calc seems to know what it is because as soon as I type =date( ... a little bubble shows up presumably indicating the syntax: DATE(<- year; month; day). There's an odd symbol to the left of the word year ...

If I just type DATE( (in other words, leaving out the "=" sign) no bubble appears.

I'm not sure if I've made any sense ...

[acknak]

... could you please explain again the part in bold?

The changes, and what I posted, only affects formulas that you create; nothing has changed regarding entering dates or any other kind of data. You can still enter a date in the current year by typing "2/16" (in English/US locale) or "16/2" (in locales that expect the day first). Using the DATE function only applies to writing a formula, not to entering a date value in a cell.

In some situations, a calculation may require that a quantity represented by some text be converted to a numeric quantity. The rules regarding such conversions have changed.

For example, suppose you have a date entered in A1, and you want to calculate how many days it occurs before a certain date, the deadline, say April 1, 2010. So you need a formula in B1 something like this: =deadline-A1. But how to write the deadline date in the formula?

Before, a user could write the date in the formula pretty much as it would be entered, but inside double quotes, like so: ="4/1/2010"-A1, and Calc would automatically convert the text into the internal numeric date value necessary to calculate the difference, following the same rules as for data entry. Ok, that's convenient and familiar.

However, if someone opens the sheet in a locale where dates are written day first instead of month first, Calc will apply the local date entry rules, and the formula will calculate the wrong result.

So now, Calc will always refuse to accept a formula with a date written that way. It requires instead that you either write the date in a way that's valid for any locale, ="2010-04-01"-A1, or invoke the entry-style conversion explicitly, =VALUE("4/1")-A1, or write the formula in a way that does not require any conversion at all: =DATE(2010;4;1)-A1.

The last option has always been the best, even though it requires a little more effort.

[vasa1]

Thanks for the detailed answer.
I guess we newbies will appreciate these things only when we run into them in a practical way.

[acknak]

PS: Oops, I forgot to include this...

Normally, changes that can break existing documents are made only when OOo changes to a new major version, e.g. 2.x to 3.x. The new major version is designed to install alongside an older major version, not to replace it, so that you can continue to run the older version if necessary for older documents that may not work in the newer version.

For some reason, this change was introduced in a minor version change (3.1 to 3.2) and if you upgrade, the older version will be replaced and you won't have the option to run a compatible older version alongside the newest version.

If you need to run two minor versions on the same system, it is possible (in some cases) with some extra effort. E.g. you may be able to create a new user and install the older version just for that user. Or, you may have to manually unpack the installation files.

[mriosv]

A good and simple solution it's install OOo Portable from http://portableapps.com/,installable anywhere on the hard disk in addition to the pendrive, without any modification in the register.

Sorry the link is
http://portableapps.com/apps/office/openoffice_portable

[vasa1]

A good and simple solution it's install OOo Portable from http://portableapps.com/,installable anywhere on the hard disk in addition to the pendrive, without any modification in the register.

Hi mriosv, the link as it is now (http://portableapps.com/,installable) gave me a "404" error. In any case, I backed up a bit and found what you meant. Can we have a pointer to a FAQ or even a topic here that deals with the portable version and just how it differs from the real thing?

[acknak]

I came across some links to other information relevant to this change:
http://www.openoffice.org/dev_docs/feat ... lc_strings
http://www.openoffice.org/servlets/Read ... msgNo=3523
http://qa.openoffice.org/issues/show_bug.cgi?id=5658

There is an extension available to help convert any numeric text values in a sheet to actual numeric values:
Convert Text To Number (and date)
http://extensions.services.openoffice.o ... oject/CT2N

The extension does not help with formulas that contain numeric text.

[acknak]

• Wrapping the numeric text with the VALUE() function to do an explicit conversion is equivalent to the old behavior in most cases.

I overlooked (at least) one common case that depends on the old behavior: formulas that expect non-numeric text to convert to zero.

Suppose cell A1 contains the total amount of an order, and B1 contains a formula that calculates a discount for the order, as a fraction. When the total order is less than some threshold, there is no discount, but, instead of a zero value, the formula returns the value "None".

In previous versions of Calc, it was possible to find the total cost of the order by a simple formula: =A1*(1-B1), because the string "None" converted to numeric zero. Now, the formula calculating the total cost fails with an error, because the string "None" cannot be converted to a numeric value. Instead, the total displays #VALUE.

How can formulas be modified to get the old behavior?

One way to get the old behavior is to wrap the cell reference in a SUM: =A1*(1-SUM(B1)). SUM ignores text values, so the SUM will be zero if B1 contains text.

Using =A1*(1-VALUE(B1)) does not work: VALUE raises an "Invalid argument" error (Err:502) if B1 contains a non-numeric string.

Using =A1*(N(B1)) used to work, and will work in Excel, but not in the current versions of Calc: it raises the #VALUE error (Issue 109861). According to the issue tracker, this problem should be corrected for OOo 3.3.

If the cell may contain "numeric text" as well (e.g. "123.45") and you want the calculation to work in that situation, you have to test and handle both possibilities:
=A1*(1-IF(ISERROR(VALUE(B1));0;VALUE(B1)).

This formula will produce a numeric value if A1 contains a number or numeric text that can be converted to a number, or zero if A1 is empty or contains non-numeric text.

Use a custom number format instead

A different approach--one that entirely avoids this messy problem--can be used when you want to display some text instead of zero: instead of creating a formula that returns a text value in place of numeric zero, use a custom number format. The cell will still contain a numeric zero, but it will display a text instead. This approach removes the clutter and complications of formatting and display from your calculations, and simpler formulas are more likely to be correct and are easier to maintain and support.

If the normal number format for a cell is 0.00, then a format like this: 0.00;-0.00;"None" will display the text "None" when the cell value is zero. Calculations that reference the cell will only see the numeric value.

This works in all versions of Calc, and Excel as well.

The down side of this approach is that it usually requires re-working the logic of your formulas. You get a better product in the end, but it's a significant investment in making the change.

[wope]

in OOo 3.2 there is a shorter posisibilty
instead of =n(a1) you must give =sum(a1) , if in a1 maybe a text
SUM with one parameter has the same behaviour as N in 3.1

[wope]

In OpenOffice 3.3 this bug will be corrected

[acknak]

Someone pointed out that Calc 3.2 will, in some situations, use numeric text in formulas without raising an error. The example given was testing whether an interval was less than 30 minutes, using this formula: C1-B1<"0:30", which produced the result TRUE and no error.

So, no error is raised, but does it actually produce the intended result? It's easy to see with a few tests that it does not:

• =0<"0:30" -> TRUE (ok)
  =1<"0" -> TRUE (no!)
  =1e6<"0" -> TRUE (no)
  =1e6>"0" -> FALSE (no)
  =0>"0" -> FALSE (ok)
  =0="0" -> FALSE (no!)

Calc is surprising us here again because the formula appears to work, but, in reality, Calc considers any text value to be "less than" any numeric value. Calc does not convert the text value to a number at all. Calc will automatically convert "0:30" to a numeric value for a calculation, but not for a comparison.

As far as I can tell, Calc and Excel behave the same way when comparing text and numbers like this.

If you find that you need to modify your formulas to work with newer versions of Calc, you might want to take the opportunity to revise any formula that depends on text values, because they could be generating incorrect results even though no error flag is raised.

In the example given, a better version of the formula would be: C1-B1<TIME(0;30;0), which gives the correct result and works in any locale because it does not require any text to numeric conversion.

[peter.black]

I've noticed an additional change from 3.1 to 3.2 that seems related to the discussion here, but I haven't seen any mention of it elsewhere. The TEXT function converts a number to a string, and now in 3.2 it appears that this produces an error if the cell reference passed to the first parameter is a string. Excel seems to quietly ignore this and simply return the string. Is this intended behaviour?

P

[acknak]

Good catch!

It does seem that the problem is related, although what happens is a bit more complicated than simply the text/number problem.

The behavior of TEXT is due to be changed for OOo 3.3: Issue 108385: TEXT should accept textual argument
You can register there and add your vote (up to two) or comment.

[sorgud]

My problem is very simple... I have cells which can be filled with either numbers or text. In the same row I average the values if they were numeric values on the cell if not the N() didn't take into account the cell value, and it was OK.
The cells represents the grade (numbers) in a test. But the student can be absent, so I put a letter (F=Falta (=absent in Portuguese and Spanish).
Then I make an average of the grades:
Example:
=(N(D7)+N(E7)+N(F7))/2
No other function solves in the same way.
In the meantime, as a workaround I try SUM. It seems to work.
Thank you.

talueguito
raul

[Davidovitch]

But what is then the consequence for the DSUM and other calc database functions? I think I figured out how to handle the data properly now, so that is not the problem any more. However, an issue remains. If the search criteria has a text value, which is a reference to another cell (so one cell of the search criteria is saying: "=B22" ( or "=T(B22)" ), where B22 is just text, nothing fancy here), the database function fails to give the correct answer. If I would replace the "=B22" with the actual textual value which was typed into that cell, there is no problem. Before, this solution worked just fine. But now in OoO 3.2 it fails to come to the same result. What am I missing here?

[Davidovitch]

I have created a bug report for this. See issue 110972

[Villeroy]

Seems to be fixed in 3.2.1
All the DSUMs in your attachment to the issue yield 4321.