| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Columns
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding Columns
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 "Kevin" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding Columns
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 "MartinW" wrote: 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 "Kevin" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding Columns
=IF(C4="","",C4-B4))
Added one more bracket at the end and it seems to work now Thanks again "Kevin" wrote: 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 "MartinW" wrote: 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 "Kevin" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding Columns
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" wrote in message ... =IF(C4="","",C4-B4)) Added one more bracket at the end and it seems to work now Thanks again "Kevin" wrote: 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 "MartinW" wrote: 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 "Kevin" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding Columns
Just looked at it again, I looks like I added two brackets the other is just
befor B3-C3 =IF(B3="","",(B3-C3)) "MartinW" wrote: 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" wrote in message ... =IF(C4="","",C4-B4)) Added one more bracket at the end and it seems to work now Thanks again "Kevin" wrote: 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 "MartinW" wrote: 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 "Kevin" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding Columns
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 "Kevin" wrote in message ... Just looked at it again, I looks like I added two brackets the other is just befor B3-C3 =IF(B3="","",(B3-C3)) "MartinW" wrote: 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" wrote in message ... =IF(C4="","",C4-B4)) Added one more bracket at the end and it seems to work now Thanks again "Kevin" wrote: 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 "MartinW" wrote: 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 "Kevin" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding columns | New Users to Excel | |||
| Adding Columns | Excel Discussion (Misc queries) | |||
| Adding columns | Excel Discussion (Misc queries) | |||
| Adding Columns | Excel Discussion (Misc queries) | |||
| adding columns | Excel Discussion (Misc queries) | |||
Linear Mode
