FrogFan wrote:I just noticed that =G4="" returns TRUE while LEN(G4)=1.
G4 is a formula reference to an empty cell. Therefore an equality text with G4 and the null string has the value TRUE. However, since Calc treats the reference as numeric, G4 shows 0, and LEN(G4) is 1! An equality test with G4 and zero is also TRUE even though ""≠0.Villeroy wrote:What is in G4 actually? I can't reproduce this. G4="" implies LEN(G4)=0
Note Cell A2 is unused, C2 is a direct copy but seemingly processed as number zero.
=IF(ISBLANK(A2);"";A2)
=IF(ISNUMBER(A2);A2;"")
Phillip wrote: but it is anything but easy for modern data analysis as the workaround
Yes I see these arguments, but I still think that =cell should give an exact copy of what was the value in the cell not amend it to a "nil". Otherwise why doesn't =cell copy over formulae?
FrogFan wrote:My problems begin when I link a cell in, say, October's sheet to a cell in September's sheet.
Right. Because you split your data in a sheet for September and another sheet for October. This is a plain wrong approach.
I presume Lupp intends to ask about a formula referencing an empty cell. Suppose A1 is empty. Enter =A1 in B1. =ISNUMBER(B1) is FALSE, as desired. =ISTEXT(B1) is FALSE, as desired.Lupp wrote:Is there a formula referencing a single cell with the effect that the cell containing the formula reports FALSE if asked ISNUMBER() and if asked ISTEXT as well?
MrProgrammer wrote:...a formula referencing an empty cell. Suppose A1 is empty. Enter =A1 in B1. =ISNUMBER(B1) is FALSE, as desired. =ISTEXT(B1) is FALSE, as desired.
Phillip wrote:NB. The fact that arithmetic works seems very odd to me as presumably B1 is a string value "2".
Phillip wrote:When I started this topic it was how to copy cells so that unused ones were still processed as unused - i.e. ignored by COUNT/AVERAGE etc, while arithmetic still worked.
...
Edit: Embedded the picture from carl2k17 -- MrProgrammer 2019-06-28 19:15 |
carl2k17 wrote:Seriously Guys?
I'm not even a noob and I found a solution for this, F the formula's
Head Over to:
Tools > Options > Open Office Calc > View > Untick Zero values
Job Done
Guide:
- Code: Select all Expand viewCollapse view
http://prntscr.com/o81xm8
=A3
Users browsing this forum: JohnSUN-Pensioner and 12 guests