Scatter Chart data #2

That would be great, but the boss does not want blank cells - if it's
blank then it is assumed the data is just not entered for that date.
That is why I created the whole shaded cells bit - unfortunately the
idea was not well received.... I guess I have to face the fact that I
will be spending a lot of time on this...Thanks for the suggestion!

Robin

Jon Peltier Wrote: 
> You can do Find-Replace to remove the asterix. Since an asterix is a
> special character, enter ~* in the Find box; make sure the Replace
> With
> box is empty.
> 
> Then teach the analyst to keep blank cells blank!
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> 
> rfronk wrote:
> -
> I have several spreadsheets that contain several workbooks each. Each
> workbook has multiple charts (xy scatter) plotting test results
> (y-axis) vs date tested (x-axis). Typically the test results are
> numbers, however, when testing is not conducted the analyst enters an
> asterisk (*). The asterisk is given a value of zero in the chart so I
> have to go to the source data and set it to exclude the data
> containing
> the asterisk - it works but is very time consuming! To get around this
> I
> have shaded the cells that are not tested and deleted the asterisk,
> changed the way Excel handles empty cells from Not plotted to
> Interpolated and made a legend on the worksheet to indicated the
> meaning of the shaded cells. This works but is also time consuming!
> Any ideas on how to make this work without so much effort? I know I
> can
> create a macro to automate the steps I have already taken but I am
> looking for a way to ignore, hide or filter non-numerical data in the
> data table - is this possible or am I chasing a pipe dream?
> 
> FYI - I am using Excel 2003 on W2K
> 
> -


-- 
rfronk
0
6/17/2005 11:59:22 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
414 Views

Similar Articles

[PageSpeed] 21

Would your boss let you use a helper sheet?
Copy all of the data to a new sheet.  format the cells to make the graphs 
look the way you want them to and then hide this sheet.
that way the imput data would be in the form your boss wants,  but the 
graphs could be made the way you want them.

"rfronk" wrote:

> 
> That would be great, but the boss does not want blank cells - if it's
> blank then it is assumed the data is just not entered for that date.
> That is why I created the whole shaded cells bit - unfortunately the
> idea was not well received.... I guess I have to face the fact that I
> will be spending a lot of time on this...Thanks for the suggestion!
> 
> Robin
> 
> Jon Peltier Wrote: 
> > You can do Find-Replace to remove the asterix. Since an asterix is a
> > special character, enter ~* in the Find box; make sure the Replace
> > With
> > box is empty.
> > 
> > Then teach the analyst to keep blank cells blank!
> > 
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> > 
> > 
> > rfronk wrote:
> > -
> > I have several spreadsheets that contain several workbooks each. Each
> > workbook has multiple charts (xy scatter) plotting test results
> > (y-axis) vs date tested (x-axis). Typically the test results are
> > numbers, however, when testing is not conducted the analyst enters an
> > asterisk (*). The asterisk is given a value of zero in the chart so I
> > have to go to the source data and set it to exclude the data
> > containing
> > the asterisk - it works but is very time consuming! To get around this
> > I
> > have shaded the cells that are not tested and deleted the asterisk,
> > changed the way Excel handles empty cells from Not plotted to
> > Interpolated and made a legend on the worksheet to indicated the
> > meaning of the shaded cells. This works but is also time consuming!
> > Any ideas on how to make this work without so much effort? I know I
> > can
> > create a macro to automate the steps I have already taken but I am
> > looking for a way to ignore, hide or filter non-numerical data in the
> > data table - is this possible or am I chasing a pipe dream?
> > 
> > FYI - I am using Excel 2003 on W2K
> > 
> > -
> 
> 
> -- 
> rfronk
> 
0
BJ (832)
6/17/2005 4:49:04 PM
bj's helper sheet would have been my next suggestion.

You could adjust the previous suggestion, so that you replace ~* with #N/A.

Could you teach the boss to recognize #N/A as a test not conducted?

Could you teach the analyst to enter #N/A directly?

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


bj wrote:

> Would your boss let you use a helper sheet?
> Copy all of the data to a new sheet.  format the cells to make the graphs 
> look the way you want them to and then hide this sheet.
> that way the imput data would be in the form your boss wants,  but the 
> graphs could be made the way you want them.
> 
> "rfronk" wrote:
> 
> 
>>That would be great, but the boss does not want blank cells - if it's
>>blank then it is assumed the data is just not entered for that date.
>>That is why I created the whole shaded cells bit - unfortunately the
>>idea was not well received.... I guess I have to face the fact that I
>>will be spending a lot of time on this...Thanks for the suggestion!
>>
>>Robin
>>
>>Jon Peltier Wrote: 
>>
>>>You can do Find-Replace to remove the asterix. Since an asterix is a
>>>special character, enter ~* in the Find box; make sure the Replace
>>>With
>>>box is empty.
>>>
>>>Then teach the analyst to keep blank cells blank!
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>
>>>rfronk wrote:
>>>-
>>>I have several spreadsheets that contain several workbooks each. Each
>>>workbook has multiple charts (xy scatter) plotting test results
>>>(y-axis) vs date tested (x-axis). Typically the test results are
>>>numbers, however, when testing is not conducted the analyst enters an
>>>asterisk (*). The asterisk is given a value of zero in the chart so I
>>>have to go to the source data and set it to exclude the data
>>>containing
>>>the asterisk - it works but is very time consuming! To get around this
>>>I
>>>have shaded the cells that are not tested and deleted the asterisk,
>>>changed the way Excel handles empty cells from Not plotted to
>>>Interpolated and made a legend on the worksheet to indicated the
>>>meaning of the shaded cells. This works but is also time consuming!
>>>Any ideas on how to make this work without so much effort? I know I
>>>can
>>>create a macro to automate the steps I have already taken but I am
>>>looking for a way to ignore, hide or filter non-numerical data in the
>>>data table - is this possible or am I chasing a pipe dream?
>>>
>>>FYI - I am using Excel 2003 on W2K
>>>
>>>-
>>
>>
>>-- 
>>rfronk
>>
0
6/18/2005 12:20:18 PM
Reply:

Similar Artilces:

Typographical error in RMS 2.0 Manual Index
In the index look-up section for "Denominations" the page is listed as page 226. It should read 224. Please correct in future publications of the manual. ---------------- 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/News...

Exchange 5.5 SMTP Relay problem #2
Hi, I currently had one Exchange 5.5 SP4 server that forward all email to Mailsweeper. Mailsweeper was configured to send outgoing mail to the following: domain1.com to xxx.xxx.xxx.xxx domain2.com to xxx.xxx.xxx.xxx all other mail to yyy.yyy.yyyy.yyyy. Due to company changes I have been asked to remove the mailsweeper box, now I am having problems doing the above with Exchange. I have already configured the IMS default "forward all messages to host" yyy.yyy.yyy.yyy and E-mail domains as specified domain1.com to xxx.xxx.xxx.xxx domain2.com to xxx.xxx.xxx.xxx This wa...

Import Contacts from Outlook 2003 into CRM 1.2
When i want to import contacts from outlook 2003 into MS CRM 1.2 its getting a real mess, any information about my contacts is standing on the wrong place, does anybody know how i can imput the contacts the good way? Bob Schenk DTA Systems BV. ...

macro for bubble chart
When I record the following code through macro recorder, then the bubble chart comes out fine. However, when I then try to rerun the same code then I get an error message: Run-time error '1004' Method 'ChartType' of object '_Chart' failed Any advice on why the xlBubble as chart type is not working? Sub Macro2() Charts.Add ActiveChart.ChartType = xlBubble ActiveChart.SetSourceData Source:=Sheets ("Sheet2").Range("A2:C6"), PlotBy:= _ xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2" End S...

Problem with plotting a chart when using arrays as Values and Xvalues
Hi I am trying to generate a chart from VBA arrays, but I am finding that once the arrays become too large I get the following runtime error: Run-time error '1004': Unable to set the XValues property of the Series class A simple piece of code which reproduces the error: Option Base 1 Sub test() Const nPts As Long = 81 'if increase this to >= 82, doesn't work! Dim x(nPts) As Double, y(nPts) As Double Dim i As Long For i = 1 To nPts x(i) = i y(i) = i Next i 'Create graph Set Graph = ActiveSheet.ChartObjects.Add _ (Left:...

How to save and restore all data
Hi ! I'm using MS Access 2003 and Windows XP On the one hand, there are regular users of our db. They only change the data in the tables. They don't change forms, queries, reports or other things. Once in a while I have to make a change to a form, a query or other non-table-data things. But in the meantime, the regular users may keep changing data in the tables. I would like to get the latest data into my new db version. Is there a way of copying just the tables to the new db version ? Is there a way of copying just the table data to the new db version ? Any helpful suggestions o...

Joining data points on a line chart
I have the following as an example set of data points to be plotted on either a line or scatter chart (doesn't matter which as I am having the same problem with both!) X Y1 Y2 1 2 1.8 2 4 3 6 4.5 4 8 5 10 9 6 12 I get a line drawn between the points for Y1, but because there is not a Y2 value for every point on the X-axis then I just get points and no line, despite a line being selected for in the options! Does anyone know how to get this line added on? Thanks In each empty cell enter =NA(); this will display as #N/A. The line on chart will be continuous. OR Click chart; use T...

Sorry, here's the data!!!
Work Order Sub Code Status Code Work Order # Asset Number MAJ 48 209996 248482 MAJ 10 242552 110140 MAJ 48 261283 266092 MAJ 10 262684 253321 MAJ 80 270587 250623 MAJ 90 278131 248482 MAJ 80 278132 248483 MAJ 80 278133 248484 MAJ 80 278137 248485 MAJ 91 312322 253966 MAJ 48 312534 254051 MAJ 48 324296 253287 MAJ 10 333233 253966 MAJ 80 333238 47488 MAJ 80 333296 47731 MAJ 90 333714 47415 MAJ 14 335426 2250 MAJ 04 335444 253317 MAJ 81 335816 254051 MAJ 10 336009 0 MAJ 91 336013 0 MAJ 48 336023 112957 MAJ 10 336409 46649 MAJ 14 337899 254074 MAJ 10 338720 47381 MAJ 10 338992 47488 MAJ 48 339020...

How do I sort a column of formulas in Excel? #2
I have a column of dates that I produced by putting in the first date. Then the next and following were simply a formula =SUM(A4+7) and so on for five years of Sundays. I wanted to have two for each sunday I could have them sorted by AM or PM so I duplicated the list and put AM in the next column beside the first 5 years of Sundays and PM beside the next five years. I then sorted with column A (dates) descending and column B (AM/PM) descending. I expected to get 01/01/2000 AM and then 01/01/2000 PM under each other and so on but the formulated column would not sort. I tried a sample of ...

Expanding y-axis in bar-chart
Hello I would be most grateful for advice on whether it is possible to magnify a fixed portion of the y-axis for a bar-chart and if so how to do this. Many thanks! ...

Latest Money 2005 update #2
Since money downloaded and installed the latest update my "My Money" file has been corrupted. Now when Money starts, entries in my accounts are duplicated and/or in triplicate. I had a back up saved of "My Money" which i reverted to, but when opened in Money a conversion process was gone through and the same happened. I uninstalled money, reinstalled it, got the latest updates again (no choice) and the same thing happens. My current account is showing -£19k. Any one else have this?? Have answered my own problem. When Money was syncronising with money on the web i...

Help with importing data
Hi All, I'm currently using Access 2002 Sp3 and I'm having troubles with importing data from a text file. The text file is 750Mb in size. Are there any handy tips or tricks that I ould using tryiong to import this data. Whenever I try to import data Access stops responding. Rgds, Dolphy With a file that size you have to do a line by line import ie write your own routine Pieter "Dolphy" <Kyrpto@gmail.com> wrote in message news:1190847728.817529.138530@g4g2000hsf.googlegroups.com... > Hi All, > > I'm currently using Access 2002 Sp3 and I'm having...

Microsoft Dynamics 4.0 Data Migration Manager Sign in Issue
Hi, OK I'm a Syatem Administrator on our companies CRM system. I have installed DMM as recommended on my PC and installed with SQL Express. The problem I have is that the DMM has installed correctly and when I try to sign in it comes up with the error of 'You must have System Administrator security role to use this tool.' I have!!. My colleague who is also a system administrator has installed successfully and has been able to sign in use the DMM. We are both on the same version of Windows (Windows 7 Enterprise) and exactly the same role permissions on the CRM. Has anyone see...

Bulk import of data in HR module
We have the HR module integrated with Payroll but are grossly underutilizing it. One goal is to be able to import a summary .csv file from our health insurance provider in order to populate the HR tables with health ins choices, COBRA info, etc. Integration Manager doesn't seem to see the HR module as a valid destination. Any way to get this (and other) data into Great Plains? Thanks! Doug There are two ways: a. eConnect. This is a programmers tool, but does makes things quite easy as it handles all the business logic. A record gets imported or rejected b. Direct table import. ...

Clustered bar chart
Hi - I posted a question last week that no-one has answered, either its impossible to do or I didn't phrase it properly! Re: Clustered bar chart I have monthly data ranges with 5 possible values, eg May has 5 sales figures, June has 5 sales figures, July 06 etc. When there are 5 non-zero values in one month the bars all touch each other and are clearly defined as being from that month. The bars represented are in a series order defined in the options. Where there are zero values in a month, the series order remains and a space appears between the bars. For example, if the data in J...

Excel Charts #9
I am trying to copy a chart from one workbook to another that are linked together. when I copy my chart and select paste in my other workbook I get the error "No more new fonts may be installed in this document". I am able to copy or move it into my other workbook, but must hit ok to this error about 15 times before it lets me proceed. What can I do to fix this? -- Sandi Gardner Hi - You're probably running into internal resource limitations in Excel. How complex is your source and target workbook? If you've got multiple charts in both you're going to run into t...

Save chart as .BMP instead of .GIF?
Can this be modified to save a .BMP instead of .GIF? Sub Save_ChartAsGif() Dim oCht As Chart Set oCht = ActiveChart 'ActiveSheet.Name On erRROR GoTo Err_Chart oCht.Export FileName:="D:\My Documents\_wip\Pic_" & ActiveSheet.Name & ".gif", Filtername:="gif" 'oCht.Export FileName:="C:\PopularICON.jpg", Filtername:="JPG" Err_Chart: If Err <> 0 Then Debug.Print Err.Description Err.Clear End If End Sub Hi, If you have xl2007 then .bmp is supported. Here is some more information about graphics filters. http://office.microsoft...

How do I make a horizontal box & whiskers chart in Excel 2007
I need to make a horizontal box & whiskers chart. Most directions are for a vertical chart. I am using Excel 2007 Jon Peltier has a technique at http://peltiertech.com/Excel/Charts/BoxWhiskerH.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dw" <dw@discussions.microsoft.com> wrote in message news:7EA22AEA-C925-43D1-B895-DC103B8039B0@microsoft.com... >I need to make a horizontal box & whiskers chart. Most directions are for >a > vertical chart. I am using Excel 2007 ...

How can I tranfer my data to my.swf in MFC program.?
I have my.swf and my.exe (MFC). I want to send to my.swf an data of my.exe. But I don't know it. Please tell me some idea.. (I want just simple flash/MFC code.. just...) On Mar 15, 7:07 am, "keandi" <kea...@lycos.co.kr> wrote: > I have my.swf and my.exe (MFC). > > I want to send to my.swf an data of my.exe. > But I don't know it. > > Please tell me some idea.. > > (I want just simple flash/MFC code.. just...) Post this in a .swf ng instead. See if ShockWave Flash object has an object model that you can use to do this. --- Ajay This mig...

Form does not show recordset's data
Hi everyone and thanks for reading I am trying to set an access form's recordset on an ADODB.Recordset that was fabricated from scratch. This means that I have appended the fields I want and then I added some records. Finally, I am setting the form's recordset property on this recordset but I get #Error in the form's controls. Offcourse, I confirm that the controlsource of each one of the controls matches the corresponding field values of the recordset. (If I set the form's recordset property on a recordset made from an existing source (table or query) the form works f...

Smartlist Report #2
When I pull the smartlist report out of Sales/Customers/Customer Sales YTD, I don't get an accurate sales number for the year to date. There is no asterick next to the report so it's one that is standard and not modified, correct? The sales number that I pull are not accurate. Do you know what exactly the smartlist is pulling other than sales, or is our report just messed up. Thanks Angie Hi Angie The * means that it is shipped with GP so if your one does not have the * then it is a user defined query. Why not restrict your query to pull transactions based on the doc...

outlook files #2
hi, how do i back up my outlook files and contacts to my usb drive and then check that it has been completed properly. Thank you Check this page for all you need to know: http://www.slipstick.com/config/backup.htm -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "kylie" <kylie@discussions.microsoft.com> wrote in messa...

Pull data (sumif?) from an external workbook
I have a workbook "report". Col A has contains products. The number of products in column A may vary but each product features only once. eg: TOTAL SALES FOR JANUARY ColA ColB ItemX 4 ItemY 5 ItemZ 4 Another workbook "sales" contains the individual sales of these items in one month. So Col A contains the products which may feature once or several times. Col B contains the Qty. eg: SALES FOR JANUARY ColA ColB ItemX 1 ItemX 3 ItemY 5 ItemZ 2 ItemZ 2 Both workbooks reside...

Save a chart as a TIFF (or other graphic file)?
Hi All, I'm an introductory Excel user. I've created some charts that I'd like to use in poster production, but I don't see how to save a chart as a TIFF or JPG. Can this be done without buying a 3rd-party solution? My workaround has been to display the chart as large as possible, the copy & paste into a graphics program. Is there a better way? Thanks in advance! hi, At the bottom of the save as box in the file type box are your save options. tiff and jpg are not one of them. >-----Original Message----- >Hi All, > >I'm an introductory Excel user....

2-sided printing?
Is it possible to code the ability to print a document on both sides of a printed page in Access? Thanks, Dean S Dean, that's not an Access function, it's a printer function. If the printer will print duplex (terminology for both sides of the paper), then it will print duplex from Access, Word, Acrobat or anything else. The applicaton doesn't know what happens to its output after it's sent and the printer doesn't care where it's coming from. UpRider "Dean Slindee" <slindee@charter.net> wrote in message news:tQkei.199$h76.14@newsfe12.lga... ...