V
Voyager62
This bug is in Excel 2003 up to the 2010 beta. In a financial spreadsheet, I
want to see zeros in the running total columns, but not in the payment
columns. In works I did this by globally allowing zero values to display,
then in the payment columns, I have a formula like this:
=IF(AV81-AU11,AV81-AU11,""). It tests whether AV81-AU11<>0 and either puts
in the difference or leaves the cell blank. In Works there's no way to do
that other than to use a null string.
Unfortunately, Excel gags on null strings. The Works foumula gives me a
#VALUE! error. The comparable Excel Formula is =IF(AV81-AU11,AV81-AU11,).
This says on a zero value, leave the cell blank. Again, in Excel there's no
other way to do it. It's too bad there isn't a compromise to use " ", but
Excel gags on that too.
If you save the file with an XLS extension, Works changes all the ""s to 0's
This makes the zeros appear in the payment columns, but allows the formulas
to work.
Believe it or not, OpenOffice Calc is fully compatible with the Works
format. It actually gags on leaving off the false value, if you leave in the
separator. It's the best of both worlds, if you want to see the zero, leave
off the false value and separator completely, if you don't, then use "". No
dashes or disconnected $ signs, and no special accounting format required.
Currency format will always keep the decimal points aligned.
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/com...9741b4e&dg=microsoft.public.excel.crashesgpfs
want to see zeros in the running total columns, but not in the payment
columns. In works I did this by globally allowing zero values to display,
then in the payment columns, I have a formula like this:
=IF(AV81-AU11,AV81-AU11,""). It tests whether AV81-AU11<>0 and either puts
in the difference or leaves the cell blank. In Works there's no way to do
that other than to use a null string.
Unfortunately, Excel gags on null strings. The Works foumula gives me a
#VALUE! error. The comparable Excel Formula is =IF(AV81-AU11,AV81-AU11,).
This says on a zero value, leave the cell blank. Again, in Excel there's no
other way to do it. It's too bad there isn't a compromise to use " ", but
Excel gags on that too.
If you save the file with an XLS extension, Works changes all the ""s to 0's
This makes the zeros appear in the payment columns, but allows the formulas
to work.
Believe it or not, OpenOffice Calc is fully compatible with the Works
format. It actually gags on leaving off the false value, if you leave in the
separator. It's the best of both worlds, if you want to see the zero, leave
off the false value and separator completely, if you don't, then use "". No
dashes or disconnected $ signs, and no special accounting format required.
Currency format will always keep the decimal points aligned.
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/com...9741b4e&dg=microsoft.public.excel.crashesgpfs