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 <[email protected]> > To: [email protected] > 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: [email protected] For additional commands, e-mail: [email protected]
