Adding Columns


K

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
 
Ad

Advertisements

M

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$3:D4))

Drag both formulae down as far as is needed.

HTH
Martin
 
K

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
 
K

Kevin

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

Thanks again
 
M

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
 
K

Kevin

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

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

Advertisements

M

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
 

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

Similar Threads

excel macro 1
How to total items if they fall between a date range 1
VLOOKUP 2
sum over a date range 7
Need subtotals 12
Formula Question 3
find values from column and place it in individual 3
Function IF statement 11

Top