incubator-ooo-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Terry <terauck-aoous...@yahoo.com.au>
Subject Re: DDE linked cells fool the "=0" test, and dismay paintbrush
Date Mon, 12 Dec 2011 05:24:48 GMT
So far, the only formula which returns a correct result from a range I created to test this
behaviour is:

=IF(ISERROR(VALUE(A3));"";A3) which returns the number if A3 contains a number, otherwise
nothing


What I tried before that was:

1 =IF(A3=0;"zero";"value") which returns "value" whether the cell contains a number, nothing
or text
2 The equivalent of that as an array formula with the same result

3 =IF(TEXT(A3;"###.##")="";"value";"zero") which returns "zero" regardless



----- Original Message -----
> From: Dave Babcock <olbab@rochester.rr.com>
> To: ooo-users@incubator.apache.org
> Cc: 
> Sent: Monday, 12 December 2011 1:33 PM
> Subject: DDE linked cells fool the "=0" test, and dismay paintbrush
> 
> OO 3.3.0, on Vista.
> I have an array A1:Z55, populated by link from a different spreadsheet. The 
> cells there are all formatted to not show 0 in empty cells (# format code).
> Entries transfer correctly, etc, and math, text, all work well.
> Except that if I do:
>         IF(A3=0;this;that)
> 
> it always does the "that", /even if /the originating cell is empty or 
> has a value of zero. Thus,
>        IF(A3=0;"";57/A3)
> will return a divide-by-zero error if A3=0, instead of giving a blank cell.
> 
> I have two workarounds:
> -don't use the # in number format.  Not acceptable for various reasons.
> -add a transparent operation, viz:    IF(A3=0;"";57/(A3*1))    This 
> works, but is hokey.
> 
> Am I missing something?
> 
> AND, this same block of linked data makes the format paintbrush go a little 
> crazy.  Maybe it has to, by some odd quirt of the DDE process. Cells can be 
> formatted individually, but not by using the paintbrush.
> 
> 
> Ol' Bab
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org


Mime
View raw message