If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Scatter Chart data
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 Last edited by rfronk : June 16th, 2005 at 03:32 PM. |
#2
|
|||
|
|||
one possibility woul dbe to select your data and use editreplace
find ~* and replace with nothing. Note the tilde ~ is needed to idedify the * as a character and not a wildcard "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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 Quote:
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
data labels for an excel scatter chart | dave c | Charts and Charting | 1 | August 11th, 2004 03:49 PM |
Data labels in scatter charts | Joshua | Charts and Charting | 1 | April 17th, 2004 06:55 PM |
Chart Source Data Ranges Changing when Data Sheet updated from text file source. | Tekn0 | Charts and Charting | 3 | January 8th, 2004 04:45 PM |