Empty Cell not empty

If I use the following formula:

=IF(B25="","",B25+C25) in Column U all the way down 
to Row 20000

to create a new value... 

(Column B has a date and Column C has a Time) and then 
copy and paste Column U to Column T as values, I see empty 
cells in Column T once my data runs out (presently around 
Row 40.

However....if I use the CountA function,it is telling me 
that I have data in Column T all the way down to Row 20000.

Since this combined Date/Time is being used as X Axis data 
it is screwing up the graph because Excel thinks those 
cells are Jan 1, 1900 when in fact they are supposed to 
be "nothing".

How can I change the original formula to prevent this or 
how can I identify and find the character in Column T and 
delete it.

I know I can easily just highlight the range and delete 
it...but I need to put it in a macro as the number of data 
points will vary over time.

TIA 
Phillip
0
anonymous (74722)
11/11/2003 1:51:06 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
811 Views

Similar Articles

[PageSpeed] 29

In the following thread, Dave Peterson suggests a couple of methods for 
clearing the cells:

   http://groups.google.com/groups?&selm=3F67D6B6.5FD7BDD4%40msn.com

Phillip Topping wrote:
> If I use the following formula:
> 
> =IF(B25="","",B25+C25) in Column U all the way down 
> to Row 20000
> 
> to create a new value... 
> 
> (Column B has a date and Column C has a Time) and then 
> copy and paste Column U to Column T as values, I see empty 
> cells in Column T once my data runs out (presently around 
> Row 40.
> 
> However....if I use the CountA function,it is telling me 
> that I have data in Column T all the way down to Row 20000.
> 
> Since this combined Date/Time is being used as X Axis data 
> it is screwing up the graph because Excel thinks those 
> cells are Jan 1, 1900 when in fact they are supposed to 
> be "nothing".
> 
> How can I change the original formula to prevent this or 
> how can I identify and find the character in Column T and 
> delete it.
> 
> I know I can easily just highlight the range and delete 
> it...but I need to put it in a macro as the number of data 
> points will vary over time.
> 
> TIA 
> Phillip


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/11/2003 3:13:07 AM
If your fake empty cells will appear only at the end of the data set, 
you can use something like =COUNTA(A1:A8)-COUNTIF(A1:A8,"").

-- 
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <076101c3a7f6$45511140$a101280a@phx.gbl>, 
anonymous@discussions.microsoft.com says...
> If I use the following formula:
> 
> =IF(B25="","",B25+C25) in Column U all the way down 
> to Row 20000
> 
> to create a new value... 
> 
> (Column B has a date and Column C has a Time) and then 
> copy and paste Column U to Column T as values, I see empty 
> cells in Column T once my data runs out (presently around 
> Row 40.
> 
> However....if I use the CountA function,it is telling me 
> that I have data in Column T all the way down to Row 20000.
> 
> Since this combined Date/Time is being used as X Axis data 
> it is screwing up the graph because Excel thinks those 
> cells are Jan 1, 1900 when in fact they are supposed to 
> be "nothing".
> 
> How can I change the original formula to prevent this or 
> how can I identify and find the character in Column T and 
> delete it.
> 
> I know I can easily just highlight the range and delete 
> it...but I need to put it in a macro as the number of data 
> points will vary over time.
> 
> TIA 
> Phillip
> 
0
11/11/2003 12:42:26 PM
Reply:

Similar Artilces:

Auto Scale X axis doesn't work with "" values in cell
First of all, thanks to tushar: http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html I'm trying to get a chart to work with the basic ranges defined as this: XValues =OFFSET('Basic Range'!YValues,0,-1) YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1) Assuming dates in A5-A16 and data in B5-B16. However, this won't work if the data in column B is equal to this "" I get the "" from an if statement: If cell in column A is blank, then cell in column B = "" or =IF(isblank(A11),"&qu...

linkingof charts in worksheets to cells
Hi Friends, I have copied some charts into the worksheets which I want to link to cells in the first sheet.I have too many charts to copy and paste into the worksheets.Is it possible to copy numerous charts into seperate worksheets (about 100 charts) as the charts are very large in size and link them to each cell in the first sheet. thanking you for you help ...

Empty the Inbox
Is there a better way to empty all messages in the Inbox than holding down the Cntrl key? Just used the laptop after several months, and it downloaded all the old emails on the server. Using Windows Outlook Express. -- kbob This is not the Outlook Express newsgroup, but anyway: If you want to delete all the Inbox messages, highlight one header viewing in the Preview Pane, and Ctrl + A will highlight them all. Delete all in one motion. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "k. bob" <kbob@discussions.microsoft.com> wrot...

Selected cell will not release
Occasionally after I have selected a cell in MS Excel 97 it will not release by left or right clicking and when I move the mouse the other cells are slected. These also will not release. The only way out of this situation I have found is to terminate the program using Ctrl-Alt-Del. This is very annoying. Is this a know problem and if so what is the solution? I have Pentium II with Windows 98 and 256 MB of RAM. John, Next time this happens, try pressing the F8 key. For more information, take a look here: http://www.mvps.org/dmcritchie/excel/ghosting.txt John "John Greenhill&qu...

have cell display the word balance when a equals the same amount a
I want a cell to display the word balance example cell "A" is the same amount in cell "B" cell "C" will say BALANCE is this possible? One of: 1] =IF(ROUND(A2,2)=ROUND(B2),"BALANCE","AUDIT") 2] =ROUND(A2,2)-ROUND(B2) Custom format C2, the formula cell (of [2]) as: [=0]"BALANCE";[<>0]"AUDIT" jenniss wrote: > I want a cell to display the word balance > > example cell "A" is the same amount in cell "B" cell "C" will say BALANCE > > is this possible? Put into cell ...

Looping until empty column
Here is my dilemma. I can do VBA in Access, but for some reason I jus don't get it in excel. What I want to do is go down a column and stor that info in a variable. I want this to happen untill there is no mor data. Once I have that data I want to go to another page and store th data in an empty column. I want the program to auto. find the empt column. Thank You in advance. thegu -- thegu ----------------------------------------------------------------------- theguz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2491 View this thread: http://www.excelforum....

Can you have both average and standard deviation in the same cell?
Or, if I want to make a chart which will show both the average and stardard deviation of mutiple samples in a bar chart graph(e.g., data like below column A and B), how should I do that? Thanks for the help! Andy A B Average STDEV 1.0 1.0 1.0 0.0 0.5 0.7 0.6 0.1 0.6 0.5 0.5 0.0 0.5 0.9 0.7 0.2 1.5 1.0 1.3 0.2 2.4 1.0 1.7 0.7 0.6 2.9 1.7 1.2 2.0 1.4 1.7 0.3 1.2 1.8 1.5 0.3 0.9 2.0 1.4 0.6 1.6 3.0 2.3 0.7 5.8 3.4 4.6 1.2 [This followup was posted to microsoft.public.excel.charting with an email copy to Andy Zhao. Please use the newsgroup for further discussion.] First, the standard dev...

Cell as Button
I can add a button to my sheet, but it seems to float over the cells. Can I add a button so that it is in a cell? Or, alternatively, change a cell so that it looks and behaves like a button? Thanks Keyser, Presumably, you want clicking the cell to activate some macro code. You can use the Selection_Change event to make any cell act as a button. This goes in a sheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then ''' put your code here, or a call to it. End If End Sub The best yo...

pst file appears empty
Hello all! I have a 450 MB pst file, that appears empty in OL98. I have run scanpst several times, did not help. I changed the first byte of the file from 21 to 00 with a hex editor and ran scanpst, it did report errors, fixed them and still no help. I tried exmerge, which reported succesful import, but did not actually retrieve anything from the pst file. Mdbvu32 also shows that the stuff is in there. It seems that the problem is quite common, but does anyone have a solution, how to fix it. Please help! Harri ...

logging date & time of data entry for individual cell
I'd like to log the time & date that content was entered into a (formerly empty) cell. When any data is manually entered into cell A1, the then-current time & date goes into cell B1 (and henceforth doesn't change). When data is entered into A2, the then-current time & date goes into cell B2. Once data has been entered into a cell, it will not be edited again. Can anyone suggest the best way of doing this? Thanks! Julz Put the following macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") If Intersec...

excluding cells from calculations by "marking" the cells?
Within a range of cells designated for a calculation, I am tyring t exclude certain cells without having to modify my function (usuall very complicated) or using conditional formatting. Example: - 10 data values, function calculates result - 2 of the data values (never the same two) are unacceptable I want these two values to be excluded from the caculation. What I nee is a way of designating these cells as "Ignore". I don't want to delet the values, nor do I want to cut out the cells. Any ideas? Thanks -- Message posted from http://www.ExcelForum.com Hi Are these '...

Empty Cell not empty
If I use the following formula: =IF(B25="","",B25+C25) in Column U all the way down to Row 20000 to create a new value... (Column B has a date and Column C has a Time) and then copy and paste Column U to Column T as values, I see empty cells in Column T once my data runs out (presently around Row 40. However....if I use the CountA function,it is telling me that I have data in Column T all the way down to Row 20000. Since this combined Date/Time is being used as X Axis data it is screwing up the graph because Excel thinks those cells are Jan 1, 1900 when in fact...

How to remove or replace a carriage return character in a cell?
After importing a Cognos "hotfile" into excel, the cell contains a carriage return character (looks like a square) and I want this to be a new line feed. I have tried to determine how to do a find and replace, but no luck. Help! Saved from a previous post: You can use Chip Pearson's Cell View addin to find out the character it is: http://www.cpearson.com/excel/CellView.htm If those box characters are char(10)'s (alt-enters), you can use edit|Replace what: ctrl-j with: (spacebar??) replace all If that box character is something else, you may need a macro: Option Ex...

interpolating blank cells
I want to graph the data from a table, but I want the charts to interpolate the lines inbetween the "blank" cells. All of the cells have formulas in them because they are pulling the data from another worksheet, but some of them appear blank because there is no data for that time slot. I have tried the Tools:Options:Charts: Interpolate blank cells, but I can't get it to work. Please help. Thanks. You see a cell with a formula is not blank Replace you formula by =IF(your-formula="",NA(), your_formula) Such as =IF(Sheet1A1="",NA(),Sheet!A1) best wishes ...

Empty Excel file is too large
I have deleted all sheets, and contents from my excel file, but it's still 2Meg in size. When I zip it, it's only 90K Bytes. (A blank excel file is usually 90K Bytes) Why is my empty file so large? It was originally 5 Meg, and I deleted all sheets, formulas, and VB codes. Right now there is nothing (except one empty sheet), but the size is still 2M. Please respond. Sometimes what I find is that Excel counts unused rows as part of the file size. So what I do is highlight all the rows from the first unused one to the last one, i.e. ctrl+g to bring up the goto box, then Ax:a65536,...

Cell relation ships.
What are cell relationships and how do i use them. Hi not a specific enough question, could be interpreted in many ways ... if this is a homework question, was there any more to the question than this? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Slukes" <Slukes@discussions.microsoft.com> wrote in message news:29C51E05-9B2F-40D5-925B-28A6B17286BB@microsoft.com... > What are cell relationships and how do i use them. This is for my GNVQ ICT coursework. We are creating spreadsheets for a customer of our choice. ...

Contacts dialogue box shows up empty
I have an issue with Outlook Pro installed on my main PC that is not recognizing contacts. They are all there, but when I try to send an email, and I select "to" the contacts dialogue box shows up empty. Thanks in advance, Jay Is the Contacts folder selected as an Address List? http://www.howto-outlook.com/faq/contactsinaddressbook.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -FREE tool; QuickMail. Create new Outlook items anywhere from within Windows -Properly back-up and restore your Outlook data ----- "Jay" <nospam_doctorpep...

ignoring empty???
Hi all, one of my students presented me with the following: "I get a list from another application. There is a column that has either value "yes" or looks like empty. Now, when I want to jump to the next row that has value "yes" I use the shortcut key Ctrl+ArrowDown. However, sometimes when a cell looks like empty there may be a space, with the result that when I need to jump to the next "yes" cell I end up in a cell that has a space. (one that looks empty) So, I used the function Trim to get rid of all the superfluous spaces in an extra column. And al...

Choosing cells based on date...
Hi! I have a cell that is supposed to add up two types of figures: - two other numbers from different spreadsheets (I've figured that out!) PLUS a series of cells on this sheet, "up to the current date". It's this last number I have no idea how to get. We're not using date fields or anything either, just a simple column system of day of the week date amount which we modify month to month. Is there a way of doing this? Hopefully I'm relatively clear... Let me know if something doesn't make sense. Wouldn't surprise me... it *is* Monday after all!...

How to put cells in a document to organize it?
I scanned 2 columns of words with 25 rows..I need be able to show the lines when I print it..I will be using it as a study sheet ..to write definitions to the words..when I scan the sheets..there are lines when I print it there are no lines.. Can someone help me ..I would appreciate it... I'm assuming the scan is now a picture. Have you tried using the contrast/brightness tools on the Picture toolbar? Or create a table over the scan and adjust it to fit. -- Mary Sauer http://msauer.mvps.org/ "yoko" <yoko@discussions.microsoft.com> wrote in message news:AA3DDAEF-...

Capture data from cell before Enter
Is there a way to capture what the user types into a cell before they hit enter? I would then feed this data into a lookup to prompt the user with the correct cell input without having to type the whole thing everytime. hi while typing into a cell, you are in edit mode and that sort of locks things up until you do hit enter. so i don't think this would be a viable solution. i would recomend that you read up of data validation. this is what data validation is for. insuring correct input. and with data validations you might be able to select the correct input from a VD drop do...

empty in box
How do I empty my in box without moving everything to delete first? I can't find a "select all" choice. Can anyone share how to do this? Thanks, Linda Highlight one message header. Ctrl+A will highlight them all. Shift | Delete will bypass Deleted Items. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Linda" <Linda@discussions.microsoft.com> wrote in message news:EC17754D-E900-4E78-8F3F-766F73CB3410@microsoft.com... > How do I empty my in box without moving everything to delete first? I > can't > f...

Calculations with empty fields
I have a report that is calculating total elapsed time between dates. I have the calculation working fine if all fields are filled in. However, out of necessity there will always be fields that are not filled in yet (because the orders have not yet arrived, shipped, etc.). I am (of course) getting the #error result for all of these cases. I would like for these fields to remain blank until the orders have arrived, shipped, etc. I am pretty sure that I need to use .hasdata, but can not quite get it to work. My expression for the calculated fields is below. =Workdays([Firs...

Center All Pictures in Excel Cells
Please help. Right now I have a UDF that I found online that lets me insert a picture into a cell by referencing the exact location on my hard drive where the picture resides. I also have a macro that if I select a shape # and range value, it will center that shape in the cell for which is referenced. Is there a way to combine these two codes to get it to insert the picture and also center the picture perfectly within the cell? I can get the first code to add as many pictures as I reference, but then I am at a loss with centering them all within the cells. Any help that you can p...

How to identify a cell [format|category|number|scientific] for sea
I currently have a project in which I need to identify a cell if it is formatted with scientific and to ignore the data value. Similar to: If objExcel.Cells(intRow,Column).xxxxx = scientific Then Else intRow = intRow + 1 Any help would be gretly appreciated Office --> Excel 2k3 using *.vbs If I record a macro while I format a cell as Scientific with two decimal places, I'd get this numberformat: 0.00E+00 And this recorded code. Selection.NumberFormat = "0.00E+00" So I could use: if objExcel.cells(introw,intColumn).numberformat = "0.00E+00"...