Blank Out Function Totals

I have the following function in cell E7 in a spreadsheet:

=D7-C7

Now, if there is no data in D7 and C7, then 00:00 appears in cell E7 as it 
has no data to work against.

What I want to do is to blank out the cells with functions in them so that 
they appear empty, unless initial data is entered. So in the above case, I 
don't want anything to appear in cell E7 until cells D7 and C7 have been 
populated.

Any ideas? 


0
scrivomc (52)
11/27/2005 1:38:26 PM
excel 39879 articles. 2 followers. Follow

4 Replies
888 Views

Similar Articles

[PageSpeed] 38

Tools>Options>View, uncheck Zero values

-- 
Kind regards,

Niek Otten

"Neil Greenough" <scrivomc@hotmail.com> wrote in message 
news:mFiif.82137$375.80579@fe3.news.blueyonder.co.uk...
>I have the following function in cell E7 in a spreadsheet:
>
> =D7-C7
>
> Now, if there is no data in D7 and C7, then 00:00 appears in cell E7 as it 
> has no data to work against.
>
> What I want to do is to blank out the cells with functions in them so that 
> they appear empty, unless initial data is entered. So in the above case, I 
> don't want anything to appear in cell E7 until cells D7 and C7 have been 
> populated.
>
> Any ideas?
> 


0
nicolaus (2022)
11/27/2005 1:58:30 PM
Neil, here is another way, put in E7

=IF(OR(ISBLANK(C7),ISBLANK(D7)),"",D7-C7)

-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Neil Greenough" <scrivomc@hotmail.com> wrote in message
news:mFiif.82137$375.80579@fe3.news.blueyonder.co.uk...
> I have the following function in cell E7 in a spreadsheet:
>
> =D7-C7
>
> Now, if there is no data in D7 and C7, then 00:00 appears in cell E7 as it
> has no data to work against.
>
> What I want to do is to blank out the cells with functions in them so that
> they appear empty, unless initial data is entered. So in the above case, I
> don't want anything to appear in cell E7 until cells D7 and C7 have been
> populated.
>
> Any ideas?
>
>


0
11/27/2005 2:13:45 PM
Many thanks both of you

"Paul B" <to_much_spam_to_list@nospam.com> wrote in message 
news:uc5BR118FHA.3952@TK2MSFTNGP12.phx.gbl...
> Neil, here is another way, put in E7
>
> =IF(OR(ISBLANK(C7),ISBLANK(D7)),"",D7-C7)
>
> -- 
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "Neil Greenough" <scrivomc@hotmail.com> wrote in message
> news:mFiif.82137$375.80579@fe3.news.blueyonder.co.uk...
>> I have the following function in cell E7 in a spreadsheet:
>>
>> =D7-C7
>>
>> Now, if there is no data in D7 and C7, then 00:00 appears in cell E7 as 
>> it
>> has no data to work against.
>>
>> What I want to do is to blank out the cells with functions in them so 
>> that
>> they appear empty, unless initial data is entered. So in the above case, 
>> I
>> don't want anything to appear in cell E7 until cells D7 and C7 have been
>> populated.
>>
>> Any ideas?
>>
>>
>
> 


0
scrivomc (52)
11/27/2005 4:23:37 PM
Another way is conditional format - set foreground colour to white
(assuming this is the background colour) if the value equals zero.

Pete

0
pashurst (2576)
11/27/2005 10:33:28 PM
Reply:

Similar Artilces:

Delete blank rows Macro
Using MS Excel 2000. Help creating a macro please. I need to search the range A1:A10000 and if the cell is blank I want to delete the entire row. Thanks in advance. -- Richard "Richard" <Richard@discussions.microsoft.com> wrote in message news:DD28F95E-58CF-4961-A3AD-E37FC68AE0F4@microsoft.com... > Using MS Excel 2000. > Help creating a macro please. > > I need to search the range A1:A10000 and if the cell is blank I want to > delete the entire row. > > Thanks in advance. > -- > Richard Something like Sub DeleteRowsIfBlank() Dim lrow as Long...

Getting an Internal Support Function Error
I am getting this message when I try to send and email to one of my distribution lists. I have even re-entered the email addresses in the distribution list. I can't figure out what is wrong I would really appriciate some assistance. I am using Microsoft Outlook, I had been using Express and transfered my data. Thanks. TK exactly what does the error say? Did you recreate the DL using a new form or just reenter the addresses in the old form? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide...

Combobox control source
I have a form link to a table: tblEmployee Fields of tblEmployee: DepartmentNumber EmployeeName Address Phone Position I have another table tblDepartment: Fields: DepartmentNumber DepartmentName DepartmentLevel DepartmentManger I have a combobox to list the Departments. So Combobox properties: ControlSource: DepartmentNumber <-- is this the field of tblEmployee.DepartmentNumber ?? Row Source Type: Table/Query Row Source: SELECT tblDepartment.DepartmentNumber, tblDepartment.DepartmentName, tblDepartment.DepartmentLe...

template function prototype
Hi, I don't know if this is the right place, but maybe someone can still help me: I have a template function, let say: template<class T> void MyFunc(T param) { .. .. .. } Now, I want to use this function in two files. Is it possible to have a prototype for this function in .h file and the implementation in a .cpp file? I tried something like: template<class T> void MyFunc(T param); in the .h file but then I got unresolved external in the file that the function was NOT implemented in.. Is it possible? Regards, Ram. Ram Baruch wrote: >Hi, >I don't know if t...

Report = Blank?
Hello, I have a report that I got from this forum, and when I use it, everything is fine. The problem I am currently having though is if say I try and generate the report using RegisterID & Date then I get a blank report...if I try one or the other, then I am fine...I pretty much tried every angle with RegisterID and with Date, and nothing...any advise would be great, as I have seen this happen with other reports as well, where the final outcome is a blank report. Thank You, Vince "When I run into problems, we more often than not fix them ourselves. Can't put a price on ...

Blank Cells #4
Hello, I am trying to do the following: 1 - In Cell A4 display the SUM of A1 + A2 + A3 if at least one of these cells is not empty. 2 - In Cell A6 display A5-A4 if both cells are not empty. How can I do this? Thank You, Miguel = Sum(A1:A3) ...and... =IF(AND(LEN(A5),LEN(A4)),A5-A4,"") -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "Shapper" <mdmoura@gmail.com> wrote in message news:25110584.2678.1325119728434.JavaMail.geo-discussion-forums@yqnd19... > Hello, > > I am trying to do ...

adding totals of sheets
I have a friend who has many Excel files, each containing data in fou sheets. She wants to add the data in the sheets together, or even th data in different files, if there's a way for her to do that. We'r using Office XP on a Windows 2000 computer, if that makes a differenc in getting advice. Any way we can add these together -- Message posted from http://www.ExcelForum.com Hi if you want for example sum all values in the range A1:A10 over several sheets use a formula like =SUM('sheet1:sheet10'!A1:A10) >-----Original Message----- >I have a friend who has many Excel...

Deleting Blank Characters in a Cell
I have a list of names with email addresses of attendees of a seminar series that I am trying to compile into a list that can be copied and pasted into an email (using BCC as the methodology for emailing). The challenge that I have is that when these files were exported from the database to excel (not sure how), all of the cells contain exactly 100 characters even though the email address may only contain 13-20 characters. Is there a way to "trim" the blank characters after the last character that is used by a letter instead of blank character field? Thanks in advance! PZan --...

Blank .msg attachments
Here's an odd one, When an email has been forwarded to, or replyed to and is using the 'attach as attachment' option (as opposed to embed text), the message is delivered with attachment, but the attachment appears blank. It is only .msg attachments that have this problem, everything else gets thru fine (.doc, ..xls, .exe etc.) The attachment is 64bytes long, and has nothing in it. Exchange 2003, Windows 2003 and Outlook 2003 being used. Any ideas anyone? -- The man with no shoes. MCSE (W2K3, W2K), MCSA (W2K3, W2K), MCSE+I (NT4), CCNA + others.... All replies to group pleas...

IF function Bug?
My IF function =IF(SUM(A1:A11)=A12, " ", "Error") is not working properly. I've used this function for 2 months now and just tonight it failed. When I say failed I mean the function returns false when the SUM=A12. I even tested it on another workbook. It only fails with the following jumble of numbers. I'm not sure if it has to do with the numbers being negative or if it's a bug. I'm using Excel 2000 (9.0.2720). Thanks in advance for the help. $480.02 A1 ($4,429.40) $2,102.82 $357.91 $100.00 $231.80 $424.02 $436.95 $84.50 ($9.54) ($50...

conditional formatting:highlight row based on blank or non-blank c
Does anyone know if, and how, is possible to use Conditional Formatting feature to automatically highlight the whole row if a specific cell in that row is non-blank (or blank)? Thank you! Hi, Yes, first select your row then in your conditional formatting select "formula is" from the drop down menu and type: =ISBLANK($A1)=FALSE or =ISBLANK($A1)=TRUE depending on weather you want the condition to apply when your cell is blank or non blank. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum....

SQL Pivot function in Access2007 ... Can it be done ?
Hi, I have a situation where I store data row by row, such as ProjectID CategoryID MonthID ValueID 1 1 1 203 1 2 1 32 1 2 2 23 1 2 3 98 2 4 2 51 2 7 2 55 2 9 4 24 etc... I need ...

Counting With Blank Rows
Hi, If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. Hi, You need to use the "count" function. If the rows in your example are 1-12 and the column is A, for the first group you'd type in (in column B) "=count(a1:a...

Between/ range function
How could I write a function that would return a number in one cell, if a number in another cell falls between a certain range? (if A1 is between C1:D1 then E1, else "0") (if .23 is between .039:.002 then 15, else "0") =IF(AND(A1>=C1,A1<=D1),E1,0) and =IF(AND(A1>=.002,A1<=.039),E1,0) -- HTH RP (remove nothere from the email address if mailing direct) "sanpanico" <sanpanico@discussions.microsoft.com> wrote in message news:F5A58078-1AE7-43B1-A773-0C75A386AFF2@microsoft.com... > How could I write a function that would return a number i...

Charting blanks as spaces
I know you can chart blank cells as spaces by clicking it in the options...this is a little different. The cell it is charting as an if function in it to display a blank cell. example: =if(D3="","",D5-D3) So when D3 is blank it should display in cell D6 a blank. But when it charts it, it charts it as a zero. I want it to chart it as a gap. I tried =if(D3="",NA(),D5-D3), This way i get a "#N/A" error in my cell, which i can hide through conditional formating. but when i click display>data labels>values in chart options it displays "#...

Blank, yet not blank
Hi, I copied some info from a database and pasted it to Excel where I wanted to manipulate the data. I notice that some of the cells, though appearing to be blank (and not showing 0 either), still responded to a simple formula like IF(OR(A1="",A1=0),1,0) giving the answer as 1. Can anyone tell me what's happening here, as it does mean I need to unneccessarily extend the formulas to exclude these cells. Rob "Rob" wrote: > Hi, > > I copied some info from a database and pasted it to Excel where I wanted to > manipulate the data. I notice that so...

BLANK FORM / QUERY
My database is pretty basic with tables, forms, queries etc. I'd like to display a form that allows me to type in a figure in the first field and the rest of the fields will populate automatically. I'm not sure how to create a query where the field awaits input. Thanks in advance. -- TomMurray In the query's criteria box, use square brackets: [Enter a value] or from a form: [Forms]![FormName]![txBoxName] in the first case, you will be prompted. In the second, you will only be prompted if the form isn't open. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http:/...

a function named variable:
To complement the built-in set of functions like C: D: I added these lines to my profile.ps1 function alias: {Set-Location alias:} function cert: {Set-Location cert:} function env: {Set-Location env:} function function: {Set-Location function:} function HKCU: {Set-Location HKCU:} function HKLM: {Set-Location HKLM:} function variable: {Set-Location variable:} All of those worked except I had to code variable: as function global:variable: {Set-Location variable:} But even then, I...

Blank Records in Catalog Merge
I am printing a directory with the following information: Name Child's Name Phone Number Address The records that do not have an entry under "Child's Name" are coming out like this: Name Phone Number Address (...with a blank space where the "Child's Name" field would be.) Even worse (and more confusing when reading the final document) is when the record has neither a "Child's Name" entry nor a "Phone Number" entry, and you get this: Name Address Basically, I want it to ignore the line if there is no data in the field. Accord...

IE version 8, bug in close current tab functionality
I am running IE version 8 on WinXP. I am reporting an issue with the current tab funtionaliy. When I close the IE browser, I get a popup to close all tabs or the current tab. By clicking the current tab the browser gets closed instead of closing the current tab. So basically, close all tabs and current tab are functioning the same way. I am surprised it was not detected in your product testing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Blank Document no longer blank
Every time Word opens, words and a picture (that were used at one time) appear as the blank document. I cannot find any place/button/option that will remove the words or picture from the default new document HELP please! See http://word.mvps.org/faqs/apperrors/blankdocnotblank.htm for tips on how to remove content from the Normal.dot(x) Hope this helps DeanH "AnaMarie" wrote: > Every time Word opens, words and a picture (that were used at one time) > appear as the blank document. I cannot find any place/button/option that will > remove the words or p...

Page shows up in print preview, but prints blank...
I'm trying to print a booklet with graphics on both the front and back covers. The back cover graphics and text appear fine in print preview, but when I send it to the printer, it prints blank. The front cover is fine. Help! Thanks in advance to anyone who can help me. How to troubleshoot problems that you may experience when you try to print to a local printer by using Office programs in Windows XP http://support.microsoft.com/kb/870622/en-us Have you checked the manufacturer's web page for your model printer? There maybe an updated driver. -- Mary Sauer MSFT MVP http://offi...

ADDING SUM TOTAL OF MORE THAN 30 CELLS IN A COLUMN TOGETHER
I am not able to add more than 30 cells in a column and need to add at least 70 - what do I do? will this work for you? =sum(a1:a100000) -- Don Guillett SalesAid Software donaldb@281.com "Robin" <Robin@discussions.microsoft.com> wrote in message news:8EBEA34A-10D3-4F58-AE0E-E9FF2EE0F73B@microsoft.com... > I am not able to add more than 30 cells in a column and need to add at least > 70 - what do I do? You can add much more than that, if indded you are using non adjacent cells like =SUM(A1,B3,C4 etc) then you can use 30, then another 30 like =SUM((1,3,5,...........

Blank Email
I have a customer with nagging and intermittent Exchange issue. Every so often when they send an email, the body of the email is blank, though there was text when they hit send. The Sent Items folder also shows the email with a blank body. There are no errors on the Exchange Server. It is happening on both the Terminal Server and on a PC, so it looks like it's Exchange Specific. Has anyone seen this? There in nothing on MS, and Google groups asks the question with no answer. Help! -Hal ...

Copy named range, but skip blanks
My goal here is to copy "NewRecord" and paste values only in the first blank cell in column A on the Payment History sheet, while skipping blanks rows. "NewRecord" has 22 rows with formulas in columns A, B & C. Sometimes all 22 rows will have data entered, sometimes only the 1st row. The marco works, but does not skip the blank rows in NewRecord - is it because there are formulas present? How do I fix this? Also, how do I assure my macro is pasting on the first blank row of column A? Application.Goto Reference:="NewRecord" Se...