On 28/04/2008 21:10, [EMAIL PROTECTED] wrote:
I just downloaded OpenOffice for the first time. I am new to this and even
new to doing much with spreadsheets. If I knew more about them, I'd be able
to find what I'm looking for. I'm trying this approach to see if I get any
helpful info back.
I am creating a spreadsheet that has a few columns of currency amounts.
Then I have a 3rd column that sums each row of the first two columns. I've
formatted the summed column as currency with red for negative numbers, thousands
separator, and two decimal columns (typical for currency). The only problem
I am having is that each of the formatted/summed cells prints out "$.00" in
each cell where the currency fields are blank. ie: They are all blank until
I have entered info into them, so until then, the calculated field contains
the unwanted "$.00".
I'm sure there must be some way of using 'if then' or 'if blank' so that
nothing is printed in the summed field until data is entered into the currency
amount fields.
HELP. Thanks for any suggestions in advance.
Ken
Click Format and then Cells. In the box labelled "Format code" you'll
see something like <[$£-809]#,##0.00;[RED]-[$£-809]#,##0.00>. (I've
deliberately used < & > instead of quotes because quotes play a
significant role in the next step.) This format code is in two parts
separated by a semicolon. Add to the right hand end of this string a
semicolon followed by two double quotation marks so that the string
becomes <[$£-809]#,##0.00;[RED]-[$£-809]#,##0.00;"">. The string is now
in three parts separated by semicolons. The Help under format codes says
====
Number format codes can consist of up to three sections separated by a
semicolon (;).
*
In a number format code with two sections, the first section
applies to positive values and zero, and the second section
applies to negative values.
*
In a number format code with three sections, the first section
applies to positive values, the second section to negative values,
and the third section to the value zero.
====
So you can now see what that third part is doing.
However, in my experience it's clearer to have something visible instead
of just a blank. So, for example, instead of adding <;""> you could add
<;"N/A"> which will make zero values appear as "N/A". I'd actually
prefer "$0.00" but ...
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
