Adding Columns

[Kevin]

A B C D E

DATE GST AMOUNT TOT - GST TO DATE
$10.00 $10.00 $10.00
$10.00 $10.00 $20.00
$10.00 $10.00 $30.00
$10.00 $10.00 $40.00
$0.00 $40.00
$0.00 $40.00
$0.00 $40.00
$0.00 $40.00
$0.00 $40.00
$0.00 $40.00

Column D is C-B
Column E is the total of D to date

How can I get rid of the zero's in D? If I do not enter an amount in C on a
given day I would like D to be blank. The way I have done it in the past is
with a conditional format If D=0 then Format the cell white. I know there
must be a better way.
Next question is if I have not entered an amount for a given day in C then I
would also like E to be blank.
It is an expense for so every line will be used, there will be no dates
without an entry. I just dont want the zero's and the TOT to carry on every
line to the bottom.

Thanks

Kevin

 

[MartinW]

Hi Kevin,

Put this in D4
=IF(C4="","",C4-B4)

Do the same to the formula I gave you for E4,
i.e.
=IF(C4="","",SUM(D$34))

Drag both formulae down as far as is needed.

HTH
Martin

 

[Kevin]

BThanks again Martin The one for E worked, but when I put the formula in D in
comes back with a true value.
This is now what it looks like...

A B C D E
DATE GST AMOUNT TOT - GST TO DATE
$10.00 $10.00 $10.00
$10.00 TRUE $10.00
$10.00 TRUE $10.00
$10.00 TRUE $10.00
$10.00 TRUE $10.00
TRUE
TRUE
TRUE
TRUE
TRUE

 

[Kevin]

=IF(C4="","",C4-B4))
Added one more bracket at the end and it seems to work now

Thanks again

 

[MartinW]

I'm glad you got it working, however, I can't see how.
The formula you posted with one extra bracket will
not work. Unless there is an equal amount of opening
and closing brackets, Excel will refuse to accept
the formula.

Are sure the sheet is working properly and not just
"appearing" to work properly?

Regards
Martin

 

[Kevin]

Just looked at it again, I looks like I added two brackets the other is just
befor B3-C3

=IF(B3="","",(B3-C3))

 

[MartinW]

OK, Excel doesn't mind extra brackets, so long as they even up.
Actually, using excess brackets is sometimes a better way
to go, it can make complex formulae easier to understand.

Glad it's all working
Martin