# incubator-ooo-users mailing list archives

##### Site index · List index
Message view
Top
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