Hiding a zero Value plus 0.2

I am using this formula, =CELL("contents",C18)*24+0.2, 
and in the blank cell it puts 0.2  Is there anyway that 
the cell can be left blank until I enter data into the 
referenced (c18) cell?  Thanks in advance for your help.

Miles Reed
0
11/2/2004 8:24:35 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
649 Views

Similar Articles

[PageSpeed] 32

Hi Miles,

=IF(C18="","",CELL("contents",C18)*24+0.2)

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel


<milesreed2@aol.com> wrote in message 
news:2f2601c4c119$f84e9820$a601280a@phx.gbl...
>I am using this formula, =CELL("contents",C18)*24+0.2,
> and in the blank cell it puts 0.2  Is there anyway that
> the cell can be left blank until I enter data into the
> referenced (c18) cell?  Thanks in advance for your help.
>
> Miles Reed 


0
nicolaus (2022)
11/2/2004 8:33:37 PM
=IF(C18="","",IF(C18=0,"",C18*24+0.2))

the cell will stay blank if c18 is a zero or blank

Any reason why you use CELL("contents")????

Regards

Bill K
<milesreed2@aol.com> wrote in message 
news:2f2601c4c119$f84e9820$a601280a@phx.gbl...
>I am using this formula, =CELL("contents",C18)*24+0.2,
> and in the blank cell it puts 0.2  Is there anyway that
> the cell can be left blank until I enter data into the
> referenced (c18) cell?  Thanks in advance for your help.
>
> Miles Reed 


0
11/2/2004 8:39:02 PM
Nick, thanks for your help, but I am still getting the same 0.2. An
other suggestions

--
mcr
-----------------------------------------------------------------------
mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1549
View this thread: http://www.excelforum.com/showthread.php?threadid=27466

0
11/2/2004 8:51:34 PM
Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany

<milesreed2@aol.com> schrieb im Newsbeitrag
news:2f2601c4c119$f84e9820$a601280a@phx.gbl...
> I am using this formula, =CELL("contents",C18)*24+0.2,
> and in the blank cell it puts 0.2  Is there anyway that
> the cell can be left blank until I enter data into the
> referenced (c18) cell?  Thanks in advance for your help.
>
> Miles Reed

0
frank.kabel (11126)
11/2/2004 8:51:57 PM
Bill, thanks.  It works like a champ.  I use cell contents because I a
referencing the cell, but I am still learing so I think I have found 
better way now with your help.  Thanks a million.

Mile

--
mcr
-----------------------------------------------------------------------
mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1549
View this thread: http://www.excelforum.com/showthread.php?threadid=27466

0
11/2/2004 8:55:13 PM
Reply:

Similar Artilces:

Extra Carriage Return Problem #2
(Sorry if this post appears twice, as I couldn't see my initial post after 4 hours) Hi, I am using XML XSL to create a CSV file using Transformation. But during the transformation all the Newline characters (CHR 10) is getting translated to Carriage Return and New Line (CHR 13 and CHR 10) causing problems in the display. Is there are any way to resolve this issue? Regards, John YS Ram wrote: > I am using XML XSL to create a CSV file using Transformation. But during the > transformation all the Newline characters (CHR 10) is getting translated to > Carriage Return and N...

Exchange 2007 and SharePoint 3.0
I folks, we are starting a new migration from UNIX to Exchange 2007 system. I have read on many blog who 2007 is the last version of Exchange that support Public folder and Microsoft suggest using Windows SharePoint v3.0 for collaboration. In our test the contact inserted in WindowsSharePoint are READONLY in Outlook 2003. How I can created a shared outlook contact list who are used in all my organization without using public folder? there are some document about SharePoint and Exchange integration? Thanks, Lorenzo Soncini Trento - Italy You can create Contacts in Active Directory - thes...

Mail #2
I have a single pop3 email account Myname@mydomain and several aliases which forward to Myname@mydomain, eg A@mydomain. When I send mail from A@mydomain in Outlook 2003 the from field shows the sender as "Myname@mydomain on behalf of A@mydomain". I would like the from field to show A@mydomain. How can I do this without setting up a different profile ...

Forcing directory replication via command line or script #2
Does anyone know if it is possible to force directory replication both within a site and between sites from the command line or via a script? I realise that I could increase the frequency of the intra-site replication in the GUI but would like to be able to do this on demand, non-interactively if at all possible. Thanks, Shane 1. Try repadmin /? OR 2. Create a file called DCList.ini In the file, type in the names of your DCs, one DC per line Save the file Start -> Run -> Replmon In Replmon, Click File -> Open Script Select teh DCLis...

copy worksheet #2
How can I copy a worksheet (tab) from a different file and still retain the links, column widths, and formatting? Seems as simple as Edit-->Move or Copy Sheet to me. >-----Original Message----- >How can I copy a worksheet (tab) from a different file and >still retain the links, column widths, and formatting? >. > ...

Excel 2000
How do you do superscripts within a cell with other text? Example: Young® (make the ® superscript) Hi select the � only and choose format / cell / on the font tab choose superscript Cheers JulieD "L Young" <LYoung@discussions.microsoft.com> wrote in message news:6B8329EE-3A64-410E-9D35-A536C49C58EC@microsoft.com... > How do you do superscripts within a cell with other text? > Example: > Young� (make the � superscript) ...

Messaging interface has returned an unknown error #2
This error messages occurs when trying to create emails and also calendar events. Does anyone know why? You may want to search the Microsoft Knowledge Base for that specific error message...there are lots and lots of different scenarios that may cause that error message to appear, and maybe you'll find an article that fits your particular scenario (Outlook version, OS version, type of e-mail account, etc.). -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:1...

Outlook 2007
Is there a newsgroup for those of us using the Office 12 Beta 2? Or do we post to the existing newsgroups? Just post right here. Patrick Schmid -------------- http://pschmid.net "Carel" <hbtec@sbcglobal.net> wrote in message news:uQDsHWl4GHA.1496@TK2MSFTNGP05.phx.gbl: > Is there a newsgroup for those of us using the Office 12 Beta 2? Or do we > post to the existing newsgroups? ...

Help! Money Plus Trial. Cannot Open My Mny File
I have two mny accounts in money plus trial. I can open mny file A. When I click to open mny file b, I am asked to type my windows live id and password, which are pre-filled form fields. I click "Sign In" When it tries to sign in, it stops and says, "Money has detected that you did not close the file "xxx.mny" before exiting. Money now needs to verify the informationin your file. Do you want to do this now? ..." I click "yes" do it now and it says " the email address or password is incorrect. Try again." Well, I've been ...

my query returns null I need to do math on the returned value
I am working on yield, I have 2 qureys one for good and the other to sort the bad in to catagorys. I want to add the total good and total bad for total tested. Sometimes the results are null. When I add the two fields I get blank when I should get units tested. Using access 2007 Thanks in advance. I use the IIF statement, eg IIF([good]>0, [good], 0) which means that if [good] is >0 then use the value of [good], else use the value 0. If [good] can be negative or positive, then use: IIF([good]>0 or [good]<0, [good], 0) Do this for both good and bad. "rich L" wrote: ...

Show zeros for null in crosstab query
Hi. I created an crosstab query and would like null cells to show a zero. is there a way to do this in the property sheet or should i use an expression? Problem is there are many columns in this crosstab and it may be cumbersome to put an expresssion in each column. Thanks for any help! Switch your query to SQL view. The first line should be something like Transform Max(SomeField) As [The Value] Change that to Transform Nz(Max(SomeField), 0) As [The Value] -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Krisp...

unable to update #2
Outlook 2002 / XP I know this appears to be a sproadic problem for many but I can't find the solution. Error message - 'Unable to update public free/busy data.' Trying not to gum up this ng, I have searched MS KB, tried to Google it, various web sites, and this ng history but the solutions I have found do not help. These include using the ClearFreeBusy switch when opening Outlook (on opening, I get the error) Checking the Calendar Options|Free/Busy options (no check marks) I found one reference to registry changes but no guidance This problem did not occur unt...

Split window in Outlook 2003? #2
Hello! I wonder if anyone knows if it's possible to split the window in Outlook 2003 so that you can see your e-mails (Inbox with preview) and your calender at the same time? I can only switch between e-mail and calender or open the calender in a seperate window. -- Andreas Andreas <Andreas@discussions.microsoft.com> wrote: > I wonder if anyone knows if it's possible to split the window in > Outlook 2003 so that you can see your e-mails (Inbox with preview) > and your calender at the same time? Outlook 2003 doesn't have that feature. > I can only switch ...

Office X to Office 11.1.0 upgrade problem
I have just upgraded to Office X 10.1.6 and wanted to go on to Office 11.1.0. When I try and load Office 11.1.0 it tells me I cannot do this - any ideas?? Phil The upgrade from Office v.X to Office 2004 (v.11) is *not* free. You will have to buy Office 2004 (since you already have Office v.X, you can get the upgrade, which is considerably cheaper than the regular edition) before you can update it to v.11.1. Michel On 27.03.05 11:22, in article 1111915374.297355.21880@z14g2000cwz.googlegroups.com, "PhilT" <phil@thethirdstage.com> wrote: > I have just upgraded to Office...

CRM 3.0 Exchange Router fills event log with errors
I've installed CRM 3.0 Exchange router according to Microsoft's documentation and the suggestions I found on this mailbox. However, the service is not working. The error message in the Event Viewer is Microsoft.Crm.Tools.ExchangeConnectorService.ExchangeSinkServiceException: Opening the inbox folder of mailbox: crm failed (System.InvalidCastException: No such interface supported at ADODB.RecordClass.Open(Object Source, Object ActiveConnection, ConnectModeEnum Mode, RecordCreateOptionsEnum CreateOptions, RecordOpenOptionsEnum Options, String UserName, String Password) at M...

Installing Outlook beta 2
I have a PC with Office 97 and Office 2000 installed. I only really use the MS Access elements of each Office version but they must stay on there. If I install the Office 2003 beta, can I keep these earlier versions of MS Access on there? I seem to recall that the beta CD doesn't make it so easy to keep earlier Office versions on there. I am only looking to have Outlook 2003 from the Office 2003 beta. That is all my need. Regards, Jon There's no Office 2003 beta that I know of - it's Outlook only. Jon wrote: > I have a PC with Office 97 and Office 2000 installed. I only r...

Sheet name as cell value
Is there a formula, or "dynamic" method of taking the sheet name an putting it as a value in the same sheet. If the sheet name changes, the cell then updates with the new name. ? -- Jo ----------------------------------------------------------------------- Jon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=317 View this thread: http://www.excelforum.com/showthread.php?threadid=27051 Hi =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,36) Arvi Laanemets "Jon" <Jon.1eds5z@excelforum-nospam....

Paste a value into "Find What"
I have a work book with data in several worksheets and one main sheet that contains all the data. Column A contains an id number. I want to record a macro that deletes a row from one worksheet, finds the corresponding row and deletes it from the main sheet, then pastes it into a sheet that contains all the deletions. How can I copy the value in column A and paste it into the "Find What" box, or is there another way to do it? I'm using Excel 2002. You've probably got something like FindWhat = "value"; edit that part of the ..Find method statement to instead r...

How to determine second (and then third) highest value in a list #2
I've got an excel spreadsheet full of data, and I wanted to create a column that would tell me what the second highest value was, given a list of cells. For example, I've got 8 columns (columns K through R), with headers "Team 1" through "Team 8". Below that, on the following MANY rows, are numbers, associated with scores for the team in that column. I'd like to have a column that tells me what score was 1st place, which was 2nd place, and which was 3rd place. Finding the 1st place one is easy, using the =MAX(K4:R4). But I'm at a loss to determine...

Counting occurences of a value tied to unique ID's
I've hit a wall on summarizing some GIS data. I've tried searching google and this group, and while I've found many posts detailing how to count uniquely within a column, the situation I'm facing is a little more difficult I believe, or perhaps I don't know the correct terminology to describe it / search for. I've simplified my actual data/situation for examples sake: Suppose column A is "ID_Number" and column B is "Comment" (for example sake "YES", "NO", or "NA"). I need to sum the number of times a specific comment, s...

customer vendor consolidation #2
I would like to see the document number used when transferring documents from payables to receivables be the invoice/document number instead of the voucher number. ---------------- 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" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/New...

Automated values in a table
I have a table which inculde two fileds. Field 1 called "Injection" is a Yes/No box. Field 2 called "#of Injections" is a number column. I want to have an automated function, where if "No" is selected in "Injection", "# of Injections" automatically fills in a value of "0". How do i do this in a table? Thankyou! Hi What if you just made the default value of the second field "0" that way you wouldn't have to worry about it if its " No" and if is "Yes" you would have to edit it anyways with a qu...

header full line but page 2 rows
Hi I like to design a report header and page over the full width of A4 paper. But all the data in 2 coloums. On Wed, 24 Feb 2010 15:45:01 -0800, Bruno wrote: > Hi > I like to design a report header and page over the full width of A4 paper. > But all the data in 2 coloums. Create a report the full width of the paper. Click on File + Page Setup + Columns Set the number of columns to 2 Uncheck the "same as detail" check box Enter the width of the columns wanted. Select "Down then Across" or "Across then Down", as wanted. -- Fred...

Checking if values are between values
I'm having a little trouble getting a formula to work. All I need to d is use Countif to determine whether or not a value is within a certai range (1.0-1.9). The formula I've tried is: =countif(a1:a25,>1.0<1.9) I'm sure there's something wrong since it's not working, but hopefull someone can advise me on the correct syntax. This worksheet is being created with Excel 97 -- Message posted from http://www.ExcelForum.com One way: =SUMPRODUCT((A1:A25>1.0)*(A1:A25<1.9)) -- Best Regards Leo Heuser Followup to newsgroup only please. "guilbj2 >"...

Outlook Error Message #2
I keep getting "Outlook.exe has generated errors and will be closed by Windows" Can anyone please tell me why this is happening? ...