charting that ignores non numeric/date cells

Is it possible for charting to ignore parts of data 
ranges which are neither numbers nor dates? (So far as I 
can tell it reads them as 0s.)
Regards,
giacomo
0
anonymous (74722)
6/4/2004 6:15:29 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
716 Views

Similar Articles

[PageSpeed] 43

Giacomo -

You should delete the non numeric cells. Excel can at least skip a blank 
cell. It can't skip an apparently blank cell produced by "" in a 
formula, because "" is a text entry even though it's zero characters 
long. If you change "" to NA() in a formula, you get an ugly error in 
the sheet, but the chart doesn't plot it.

If you're stuck displaying the text within the data table, you could 
make another table elsewhere, linked to this one, but without the text. 
Use the new table as your chart's data range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Giacomo wrote:

> Is it possible for charting to ignore parts of data 
> ranges which are neither numbers nor dates? (So far as I 
> can tell it reads them as 0s.)
> Regards,
> giacomo

0
DOjonNOT (619)
6/5/2004 2:26:59 AM
thanks Jon,
I've (almost completely) succeded via filtering out of 
the data range blank cells (nonblank). I don't like the 
fact that it hides all the rows corresponding to the 
blank column cells in the data range and I haven't been 
able to fix that (help!). But charting wise it works 
perfectly.
By the way substituting "" with na() does not work as the 
chart still puts the na values on the axis.
Thanks for your time,
gicomo

>-----Original Message-----
>Giacomo -
>
>You should delete the non numeric cells. Excel can at 
least skip a blank 
>cell. It can't skip an apparently blank cell produced 
by "" in a 
>formula, because "" is a text entry even though it's 
zero characters 
>long. If you change "" to NA() in a formula, you get an 
ugly error in 
>the sheet, but the chart doesn't plot it.
>
>If you're stuck displaying the text within the data 
table, you could 
>make another table elsewhere, linked to this one, but 
without the text. 
>Use the new table as your chart's data range.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Giacomo wrote:
>
>> Is it possible for charting to ignore parts of data 
>> ranges which are neither numbers nor dates? (So far as 
I 
>> can tell it reads them as 0s.)
>> Regards,
>> giacomo
>
>.
>
0
anonymous (74722)
6/5/2004 7:39:22 AM
Giacomo -

What you could do is filter the data, copy it, use Paste Special from 
the Edit menu to Paste-Link it elsewhere in the worksheet (where it 
won't be seen), and use this new range for the chart's data source. 
Since you linked it, updating the original data will change the chart.

What kind of chart are you making? The NA() doesn't appear on a value 
axis, but it does on a category axis. So a line chart will have an #N/A 
category, but with no marker if the Y value is also #N/A.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

giacomo wrote:

> thanks Jon,
> I've (almost completely) succeded via filtering out of 
> the data range blank cells (nonblank). I don't like the 
> fact that it hides all the rows corresponding to the 
> blank column cells in the data range and I haven't been 
> able to fix that (help!). But charting wise it works 
> perfectly.
> By the way substituting "" with na() does not work as the 
> chart still puts the na values on the axis.
> Thanks for your time,
> gicomo
> 
> 
>>-----Original Message-----
>>Giacomo -
>>
>>You should delete the non numeric cells. Excel can at 
> 
> least skip a blank 
> 
>>cell. It can't skip an apparently blank cell produced 
> 
> by "" in a 
> 
>>formula, because "" is a text entry even though it's 
> 
> zero characters 
> 
>>long. If you change "" to NA() in a formula, you get an 
> 
> ugly error in 
> 
>>the sheet, but the chart doesn't plot it.
>>
>>If you're stuck displaying the text within the data 
> 
> table, you could 
> 
>>make another table elsewhere, linked to this one, but 
> 
> without the text. 
> 
>>Use the new table as your chart's data range.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Giacomo wrote:
>>
>>
>>>Is it possible for charting to ignore parts of data 
>>>ranges which are neither numbers nor dates? (So far as 
> 
> I 
> 
>>>can tell it reads them as 0s.)
>>>Regards,
>>>giacomo
>>
>>.
>>

0
DOjonNOT (619)
6/5/2004 3:50:29 PM
Reply:

Similar Artilces:

"unable to display all the cells due to low memory"
I have a co-worker getting this error whenever I try to open my Tasks folder. He has a lot of tasks (1000+), but they are all very small with no attachments. Running Outlook 2000 on Exchange 2000. Thanks! -- Remove 'spam' from email address to contact me directly ...

how do i automatically archive old cell contents to a separate .
I have a worksheet that contains lists of "action items", that is used to keep track of jobs that need to be done. I want to be able to automatically move the contents of a row of cells to another worksheet once the action item has been completed. In other words, once I have changed the value in a cell to "yes" (the action has been completed), the information is archived in a separate worksheet. Is this possible, and how? I'd keep them in place, but use Data|Filter|autofilter to show/hide what I want. I think it makes life much more simple--especially when you...

Charting Beginning Revenue and Changes Over a 12 year period
Hi I would like to create a chart that shows revenue for 1996 and cumulative year over year changes in revenue that when added to 1996 (beginning balance) will equal 2008. Does a particular chart come to mind? Thanks in advance. Al Charbonneau Waterfall? http://peltiertech.com/Excel/Charts/ChartIndex.html#Waterfall - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 2/26/2010 10:59 AM, Al Charbonneau wrote: > Hi > > I would like to create a chart that shows revenue for 1996 and cumulative > year over year chan...

Date with VBA
Hello, I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF Hi Jeff do you really need to use VBA or would the following formula in Sheet 2 work for you? =MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,".","/"))) entered using con...

Date
Hello, I am tracking the response from suppliers, the status is: "On time" or "Late". [D3Date] is I received the response from suppliers. =IIf([D3Date] Is Null,IIf(Now()-[issueDate]>1,"Late"," "),IIf([D3Date]-[IssueDate]>1,"Late","On Time")) When I issue a ticket, shold be reponse in the following work date, if not is "Late", But if I issue on Friday, then the following work date is Monday, then the expression doesn't work right, how can I avoid the Staturday and Sunday. Thank you! ...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

Issue with cell ranges and formulas
I've got an interesting issue that I'd like some help with. I'm doing a performance analysis where I'm trying to average some numbers. Here's what I did to create the worksheet: In cell A1 enter 'Time', in B1 enter 'CPU #0', in C1 enter 'CPU #1', in D1 enter 'CPU #2', in E1 enter 'CPU #3', and in F1 enter 'Average'. I then format the cells in column A to use the 'time' category and '1:30 PM' type. Now I enter in cell A2 '8:30 PM' and A3 with '8:35 PM'. Next enter '17.368' in ...

Help for Excel Chart problem
Hi I have a little problem. I have to prepare audience profile for some TV Channels using dimensions age, sex, monthly income and education. I want to combine metric - age and income in one chart and non metric - sex and education in other. So I dont know which chart to use. I think the most appropriate is the bubble chart (more I have the penetration as a bubble size). But I cannot think how to dispose the dimensions and data. Example: Channel 1 Age 12-18 30 % 19-34 20% 35-64 35% 64+ 15% Sex Male - 48% Female - 52% So I need to positioning this channel and compare with ot...

Plot line on chart
I have a column chart which the columns show data series for the year (2010-01, 2010-02....) I need to plot a line starting at 0 and go all the way to the last entry total which would be 2010-12. So basically i am just drawing a line from 0 the the last data point. Any idea on how to get this done? Thanks in advance ...

Splitting cells into multiple records
Hello, Here is what I am trying to do. I have an access DB that Excels pulls info out of and puts in a nice little format. My issue is that some of the fields have multiple records in it. Here is an example of the DB: First Name | Last Name | Issue | Remediation You would only have one first and last name, but there might be multiple instances in the issue and remediation cells. First Name | Last Name | Issue | Remediation John |Smith | Issue Number 1 | Remediation Number 1 | Issue Number 2 | Remediation Nu...

date and sending message
I am trying to create a macro that sends a specific number to a specific cell on a certain day. "If the first day of the month, then send 75 to cell A2 If the second day of the month, then send 75 to cell A3" etc. for the whole month. Can some one help me Please Thank -- Message posted from http://www.ExcelForum.com something lilke Sub ifdate() If Day(Now()) = 1 Then range("a2")=75 'MsgBox "Hi" If Day(Now()) = 2 Then range("a3")=75 'MsgBox "Hi" End Sub -- Don Guillett SalesAid Software donaldb@281.com "rpahl >" &l...

PowerPoint 2007 with EXCEL Chart
Good afternoon, Thanks in advance. I hope I am in the correct group since this is both an Excel and PPT question - I created a Spreadsheet with 10 tabs, on each tab there is one fairly simple chart with a secondary axis that show a % for one column. I have the chart with format looking correct in Excel, and copy / paste to a corresponding slide in ppt. Sometime, not all of the time the format changes especially on the secondary axis, it goes to a decimal value. A lot of the time the data labels will be missing from the columns as well. Not sure what I am missing, USUALLY ...

New emails show beyond next month date
Hi guys As of yesterday, my new emails are showing a date of from 4 August 2010 and today they started showing a date of 9 August 2010. This is hapening to a few clients of my clients as well and Im not sure what is causing it. All the email acounts are on POP and I have checked the system date on all the machines affected and all the machines have the correct dates on them. What could be causing this? -- Poodle Did you check the date on the actual mail server? "Poodle" <Poodle@discussions.microsoft.com> wrote in message news:F58EDFCC-0503-410B-AA9B-27AC...

Transfer Delivery Date
Hi! A few months ago I noticed Money 2006 started messing with my transfer dates when I am transfering money between accounts. This even caused me a nice NSF charge. NOT happy about that. However, why all of the sudden can you no longer transfer money without a 4 day wait period. I even called my bank and they couldn't tell me (Compass). For example, today is the 7th. If I do a transfer, I can set it up but the date says the 11th and I can't change it. Any suggestions would be greatly appreciated. Thanks! This is bank specific. My bank US Bank allows me to do an XFER ...

Filtered cells return after save???
Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of...

Can you change the # of places for percentages in xcel charts
I have some data that = less than 1% of the total amount, and want to display it as 0.xxx instead of "0" percent. Is there a way to change the percentage format to include more than one # place? Hi, Select the cells you want formatted and click the Increase Decimals button on the Formatting toolbar as many times as necessary. -- Cheers, Shane Devenshire "Headmistress05" wrote: > I have some data that = less than 1% of the total amount, and want to display > it as 0.xxx instead of "0" percent. Is there a way to change the percentage > format ...

Auto copy data rows between sheets depending on date entry
Hi, I’m having problems finding a way to auto copy rows of data from one worksheet to another depending on a date entry within the data row. I’ve created the following simple example to illustrate what I’m trying to achieve. I have a workbook containing 13 worksheet tabs. First worksheet is named ‘Raw data’, then 12 worksheets follow named ‘January’ to ‘December’. I enter rows of data into the first worksheet like the example below: ‘Raw data’ worksheet A B C D E 1 Nº Date Forename Surname Address 2 25 Mon 6 Ja...

Xml Serialization
I would like to serialize a particular class into XML skipping the root element that is automatically created for the class. For example if I have the following class: public class Person { [XmlElement("name")] public string Name; [XmlElement("age")] public string Age; } I would like the following result: <name>someName</name> <age>100</age> Without the element "<Person>" that would usually be created. The reason for this is I have a class that represent several Xml elements, and when I serialize that class I...

Exchange 2007
Hi there, please help me. I have Exchchange 2007 (final-tryout) and 4 accepted authoritative domain with 4 email address policies (each domain have one of email address policy). My users has 4 smtp addresses (user@domain1, user@domain2 ...). Default email address policy (priority 1) is domain1. So if I'm trying to an send email from user@domain1, everything is ok. But if I'm trying to send an email from user@domain2 (or domain3 or domain4) that outlook said me something like "You are not authorized to send email as this user". But if I change default email address po...

Footer date format #3
How do I change the date format for my excel worksheet footers? Hi "imran", Change your Regional Date format in your system Control Settings. Or use a macro http://www.mvps.org/dmcritchie/excel/pathname.htm#modify --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Imran" <Imran@discussions.microsoft.com> wrote in message news:A1187E71-49C7-4DA1-9456-912FFEB92B2C@microsoft.com... > How do I change the d...

How to combine text from 3 cells into 1 cell
How to combine from 3 text-cells into 1 cell with space between them? See your other post for answer. John "JOF" <JOF@discussions.microsoft.com> wrote in message news:35A395E5-81A3-428E-BED7-1DF41F5370FF@microsoft.com... > How to combine from 3 text-cells into 1 cell with space between them? =A1&" "&B1&" "&C1 or =CONCATENATE(A1," ",B1," ",C1) "JOF" wrote: > How to combine from 3 text-cells into 1 cell with space between them? try this if your text are in a column A in B2 =A2&" "&...

Text to Date Conversion
This is probably an age old question, so apologies in advance... I'm trying to import data into Excel from a csv file that contains a date field of the format "Mon 01 Jun". There seems to be no easy way to get Excel to recognise this as a date, unless anyone can tell me differently. Assuming there isn't a quick import / text-to-columns conversion, what is the easiest way with a function to create a new cell that contains a date from this text? Thanks, Richard. With the text in A1; assuming all months are 3 characters =DATE(2009,MATCH(RIGHT(A1,3),{"Jan",&q...

Write scatterplot point ID to a cell
Hello all I'm trying to click a point in a scatterplot and have its ID (label, values, whatever) written to a cell. Any ideas? Thanks jcamoes Hi, Have a look at Jon Peltier's article "Chart Events in Microsoft Excel" http://www.computorcompanion.com/LPMArticle.asp?ID=221 Cheers Andy jcamoes wrote: > Hello all > > I'm trying to click a point in a scatterplot and have its ID (label, > values, whatever) written to a cell. Any ideas? > > Thanks > > jcamoes > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks! That&#...

Formula to copy multiple cells onto another sheet
I have a worksheet containing names and address that are repeated onto other sheets based on criteria in another column. What formula if any can I use to determine if that person meets that certain criteria to automatically copy the 3 columns that the name, phone, and address are located in to another sheet? A little more info: I have multiple worksheets and one master list containing 6 columns: First, Last, Birthday,Phone, and Address, and class. In the class column is a variety of class names. I would like to write a macro (I have never done this before!) ,if possible, that will ...

Sticking cells
BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 and 2 on my spreadsheet to 'stick', so when I scroll down my sheet the top 2 rows are always at the top. How can I do this? Thanks in advance. -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" "Laura ( '_' )" <laura@invalid.example.com> wrote in message news:u%235COkd8FHA.4012@TK2MSFTNGP14.phx.gbl... > BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 > and > 2 on my spreadsheet to 'stick', so when...