Works to Excel Conversion Bug

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
 
U

USAOz

I hope this not regarded as an unhelpful reply, but have you tried changing
the format of the number AND the way in which you test/display null values?

I would change the display of the cell(s) to Accounting (with whatever
number of decimals you require) THEN change the formula to read:

=IF(AV81-AU11,AV81-AU11,0)

Maybe this will help. Let m know if this was what you were looking to do.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top