On 04/11/2007 10:12 AM, Dan Cox wrote:
> Here's one that I am sure will get plenty of replies:
>
> I am setting up a spread sheet to calculate payments, balance owed,
> etc, and I have 25 rows that can have data input (just to keep it all
> on one sheet). How do I keep the calculating formula, but make the
> cells stay blank (not show $0.00) if I do not put name or some other
> identifier into that row. In other words, how do I get only the rows
> that need to have calculated to show calculations, and have the
> remainder stay blank, but still ready to accept data. I do not want
> to hide the rows, because I am not the one who will use this
> spreadsheet, and that has already caused me messes in the past. Thank
> you in advance for the replies.
>
This is directly from a cash control journal that I'd set up:
=IF(I20<>0;I20-(G20-H20);0)
IF(Test;Then_value;Otherwise_value)
Modify to suit your needs. In the above:
- I20 does not equal 0 then use formula I20-(G20-H20)
- IF I20 does equal 0 then the value is set to 0
In this example:
I20 = account balance
G20 = receipts
H20 = payments
To get it to not show $0.00 the easiest way that I can recommend is get
rid of the currency formating altogether. Unless you are dealing with
mixed currencies just use number formating. Cell format for the above is:
Number: (1,234.12) (red)
Decimal places: 2
Leading zeros: 1
Negative numbers red
Thousands separator
Alignment: Right
If you really want to show the $ sign, use:
Number: ($1,234.12) (red)
and that will give you a dollar sign with the same result.
If you use currency vs number you will get $0.00 unless you modify the
definition.
Number:
"$"#,##0.00_);[RED]\("$"#,##0.00\)
Currency:
[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
