[Solved] Formula returns zero

[rofson]

I am using the formula =IF(C4=C3;D4+H3;D4). If C4 does NOT= C3 then the result is the value in D4 (as expected). If C4 does = C3 then the result is always 0. There are positive non-zero numbers in D4 and H3 so the formula should not be zero. I am new to OpenOffice but have used Excel for years.
Thanks

[Dave]

It is likely that D4 and H3 are in text format. You'd have the same result in any spreadsheet if that is so.

David.

[acknak]

You'd have the same result in any spreadsheet if that is so.

Well, not quite. In a calculation, Excel will automatically convert a text value that looks like a number into the corresponding numeric value, whereas Calc will convert any text value to zero, even if it looks like a number. So a sheet that seems to work fine in Excel will fail in Calc with just these symptoms.

You can see if you have a "numeric text" entry by selecting a cell, then looking at the contents displayed in the formula bar. Numeric text will have an apostrophe at the beginning. Edit the cell and remove the apostrophe--or just re-type the number--and you'll have a pure numeric value. If you have many cells to fix, find and replace can do a batch conversion.

[Dave]

Actually, will not Calc display numerical and text data in different colours?

David.

[rofson]

Well this is interesting. Thanks to all for the replies but this is what I am seeing.
You are right, the cells I am tring to add are labels but there is no indication of that in edit the cell, no apostrophy. I can see that it is a lable using the CELL("type") function and I can test that there are beginning spaces. Any idea why I don't see the apostrophy? Does anyone knows why?

Note: knowning that the cells were text I tried using a combination of CLEAN() and VALUE()which didn't work. But I get Or if CLEAN() doesn't work (it doesn't) then strip the 2 leading spaces by using RIGHT(ref;len(ref)-2) and applying value to that.

Note2: remembering to use ; instead of , in formulas is going to be an issue for me.

[Villeroy]

Actually, will not Calc display numerical and text data in different colours?

Calc can display constant numbers in blue font if you check menu:View>Highlight Values [Ctrl+F8]. Floating point numbers, boolean, dates, times, scientific numbers, percent values and fractions are all numeric formats unless they are text values actually. Value highlighting will show all formulas in green font, which does not help to separate numeric results from textual ones.

It is likely that D4 and H3 are in text format.

No, no, no! Stomp, cry, rolling on the floor! There is no such thing as "text format". Text and number are like fire and ice. It has nothing to do with different representation of the same value. Number 2000-12-31 and text "2000-12-31" may be the same in our heads. But any spreadsheet will put it

You'd have the same result in any spreadsheet if that is so.

Indeed, any spreadsheet except for Excel 2003 and 2007 with some dangerous option "guess values from strings" which is set by default.

[acknak]

You are right, the cells I am tring to add are labels but there is no indication of that in edit the cell, no apostrophy. I can see that it is a lable using the CELL("type") function and I can test that there are beginning spaces. Any idea why I don't see the apostrophy? Does anyone knows why?

The apostrophe is only shown if, without it, the entry would be interpreted as a numeric value. In other words, the apostrophe is shown in the formula bar because, if it wasn't there and you typed 'Enter' causing Calc to re-interpret the entry, the entry would change from text to a number.

It would be bad form for Calc to make that change all on its own, so it sticks the apostrophe in there to prevent the change from happening, preserving the value's type as text. If a re-interpretation of the entry would not change its type, then no apostrophe is needed and it isn't provided.

So, why would the apostrophe not be needed?

One reason that if the value doesn't look like a number. The text string +1234 will be interpreted as a number, but the text string 12+34 will not. A cell will show the apostrophe for the former, but not for the latter value.

Another reason is if the cell is formatted as text (Format > Cells > Numbers > Category: Text). In that case, anything you enter in the cell will be treated as text, even something like 1234, so the apostrophe is not needed and not shown.

I don't know of any other reasons it might not appear, but there probably are some. This aspect of all spreadsheets is way too complicated.

As Villeroy already mentioned, you can also try Tools > Options > OOo Calc > View > Display > Value highlighting, but I find that feature a) inconvenient to switch on/off and b) hard to interpret (poor choice of colors; what does green mean?) and c) not always relevant: I don't care if it's a formula, is it text value or a numeric value?

One approach might be a "detective" tool that could visit the cells that contain numeric text and are used in calculations, and offer to convert them to numeric values.

[Villeroy]

One approach might be a "detective" tool that could visit the cells that contain numeric text and are used in calculations, and offer to convert them to numeric values.

July 2013: SpecialCells 0.7.4 does not show any tool bar in AOO 4 and in the latest versions of LibreOffice. The following macro code is supposed to be used with any kind of user-defined GUI element (menu, tool bar, form button, hyperlink, event, whatever)

Code: Select all

REM  *****  BASIC  *****
REM Provisional starters for the SpecialCell extension

REM Macro URLs for this module in global Standard library:
REM vnd.sun.star.script:Standard.Module1.ContentsDialog?language=Basic&location=application
REM vnd.sun.star.script:Standard.Module1.FormatsDialog?language=Basic&location=application
REM vnd.sun.star.script:Standard.Module1.HelpFile?language=Basic&location=application

REM call the contents dialog
Sub ContentsDialog
serv = createUnoService("name.AndreasSaeger.SpecialCells.DialogCellContents")
serv.trigger("")
End Sub

REM call the formattings dialog
Sub FormatsDialog
serv= createUnoService("name.AndreasSaeger.SpecialCells.DialogCellFormatRanges")
serv.trigger("")
End Sub

REM call the help file
Sub HelpFile
serv= createUnoService("name.AndreasSaeger.SpecialCells.ConfigProvider")
serv.trigger("#_TOP")
End Sub

<Advertisement subject=SpecialCells>
Inspect and navigate all properties of selected cells or current sheet's cells respectively.
Increase your productivity, spreadsheet security and reliability
Localizable user interface! Current version 7.1 includes (my broken) English, German and French[*]
Highest possible level of security since it does not change a single byte in your documents.

Contents and Visibility
Detect all constants, annotations and formula results, numbers, strings and error values, visible and hidden cells, while loading dialog "Cell Contents".
Select any combination of all detected types.
Navigate detected types using lists of detected ranges.

screenshot contents SpecialCells 7

Detect all uniquely format ranges while loading dialog "Formatted Ranges"
Select all equally formatted ranges in grouped mode or any sequence of equally formatted cells in sequential mode.
Navigate all group-members of equally formatted ranges using lists of detected ranges.
Get information about style names, hard formatting attributes, ranges with different validations and/or conditional formatting.
Groups of unique format ranges:

screenshot grouped formats SpecialCells 7

Sequence of unique format ranges:

screenshot sequence formats SpecialCells 7

Download the fully functional free version NOW.

[*] Special thanks go to the most valued, famous, incredible and fearless member of this forum Hagar de l'Est
<Advertisement>

And then there is Convert Text To Number (and date) 1.1.2, which tries to be so smart, but can not handle multiple range-selections, so it cooperates with my tool's range selections only if a single range has been selected.

[acknak]

Nice!

Sorry I forgot about that; it's probably where I got the thought in the first place