Expanding Excel

  • Thread starter Thread starter Ian Johnson
  • Start date Start date
I

Ian Johnson

Apologies if this is posted in the wrong Excel section.

I own a small chemical company that analyses various
products for chemical impurities. I have a
spectrophotometer connected to my PC that analyses the
product and then exports the data into Excel and producs a
graph. This is done via a plug-in for Excel produced by
the spectro manufacturer.

What I would like to do, if it is possible, is to have
manually entered into Excel (or an Access Db) by myself, a
library of information on all the impurities I come across
during my daily business. I would then like to be able to
overlay graphs of these impurities on top of the graph
produced by the spectrophotometer software to allow for
easy and instant recognition of any impurities in the
products I'm testing. I'm thinking of maybe something like
a drop-down box to access the information in the "library"
and then another button that would produce the overlayed
graphs.

I'm reasonalby proficient in Access if it is easier to
hold the data there and then have Excel produce a
graph "on-the-fly" to overlay on top of the spectro graph.
Or maybe it is easier to keep everything in Excel?

Any help would be gratefully appreciated as I just don't
know where to get started.

Many thanks,
Ian Johnson
 
Speaking as a chemist, I think you are asking too much of Excel for this
project
If you had the wavelength and relative heights of major peaks in digital
form, it might be possible

best wishes
 
Hi Bernard,

Thanks for your comments. I have the info from the spectro
passed straight into Excel and once I've manually keyed in
the data for the impurities (tedious and labour intensive
I know) it's a case of getting Excel to produce overlayed
charts.

Any thoughts?

Ian.
 
As long as the axis scales, at least the categories, are aligned, this might be
fine. I find it more reliable and somehow more pleasing to get everything into one
chart.

The impurity data only needs to be entered one time, right? You can save data for
many impurities in an extra worksheet.

Make the chart from the spec data, then copy the X column and Y column for your
impurity/ies, select the chart, and use Paste Special from the Edit menu to add the
data as new series. You might have normalized values for the impurity, or use a
secondary Y axis, so the scales are comparable.

To allow you to thumb through all the impurities, you could use a technique like one
of these:

http://peltiertech.com/Excel/Charts/ChartByControl.html

The main spectrum would be from the analyzer, and a second dynamic series from your
impurity data. Maybe use a combobox or spinner to march a dynamic range through all
the columns of your impurity sheet, so that one by one you can compare them, hopping
over any which do not match, and stopping to examine any particular ones more closely.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks for your replies, there seems quite a bit to take
in but I'll work through your suggestions and use the
links you have provided. I'm sure if I get stuck I'll be
back to this forum.

Thanks again, very much appreciated.
Ian
-----Original Message-----
As long as the axis scales, at least the categories, are aligned, this might be
fine. I find it more reliable and somehow more pleasing to get everything into one
chart.

The impurity data only needs to be entered one time, right? You can save data for
many impurities in an extra worksheet.

Make the chart from the spec data, then copy the X column and Y column for your
impurity/ies, select the chart, and use Paste Special from the Edit menu to add the
data as new series. You might have normalized values for the impurity, or use a
secondary Y axis, so the scales are comparable.

To allow you to thumb through all the impurities, you could use a technique like one
of these:

http://peltiertech.com/Excel/Charts/ChartByControl.html

The main spectrum would be from the analyzer, and a
Click to expand...
second dynamic series from your
 
Back
Top