How do i keep cells blank until the formula is used?

[Guest]

I am working on a timesheet that add time and calculates cost. The formula I
am using for column I of the timesheet (which can be used for up to three
in/out times per day) is "=SUM((D12-C12)*24,(F12-E12)*24,(H12-G12)*24)".
Column J calculates cost using the formula "=I12*B12" where column B is the
hourly fee.

My question is, how can I get columns I and J to be blank until they are
needed. Right now they fill with "0.00" and "$0.00."

 

[Guest]

Hi Ellen

Menu Bar, Tools, Options, View Tab. Unckeck box "Zero Values".

PB

 

[Guest]

First of all, you don't really need sum. And the way that many people get
rid of unwanted 0 values that are the results of formulas is to wrap the
formula in an IF statement that checks whether the formula will result in a
0. For instance, the following formula should work for you:

=IF(COUNT(C12:H12)>0,(D12-C12)*24+(F12-E12)*24+(H12-G12)*24,"")
Assuming that the numbers in the range C12:H12 will be numeric, this formula
worked for me. And then for the other formula:
=IF(I12<>"",I12*B12,"")

You could use SUM if you want, and just wrap it in something similar. For
instance you could say =if(sum(...) = 0,"", sum(...)

HTH

 

[Roger]

Hello PB

On this subject, I have the 'Zero Values' unchecked, but would like to also
hide the dashes (-) a zero balance still displays. Can I do this?

Thanks

Roger

 

[Dana DeLouis]

but would like to hide the dashes (-), a zero balance still displays.

One option is to use a custom format that hides zero values. Perhaps
something like this...
0.00;0.00;;

On your formula, one idea would be to factor out the 24...
=24*SUM(D12-C12,F12-E12,H12-G12)