Thread: 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


--
rfronk

one possibility woul dbe to select your data and use <edit><replace>
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
>

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
>
>