Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i keep cells blank until the formula is used?
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." |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i keep cells blank until the formula is used?
Hi Ellen
Menu Bar, Tools, Options, View Tab. Unckeck box "Zero Values". PB "Ellen" wrote: 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." |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i keep cells blank until the formula is used?
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 -- Kevin Vaughn "Ellen" wrote: 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." |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i keep cells blank until the formula is used?
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 "Pancake Batter" wrote in message ... Hi Ellen Menu Bar, Tools, Options, View Tab. Unckeck box "Zero Values". PB "Ellen" wrote: 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." |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i keep cells blank until the formula is used?
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) -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Roger" wrote in message ... 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 "Pancake Batter" wrote in message ... Hi Ellen Menu Bar, Tools, Options, View Tab. Unckeck box "Zero Values". PB "Ellen" wrote: 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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula that returns Col A data in Col B, but omitting blank cells | Excel Worksheet Functions | |||
how can I count blank cells as a zero, when using formula please?? | Excel Worksheet Functions | |||
How do you nest the following formula to show blank cells | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) |