ATTN: MSExcel buffs

[AndySlash]

Also, if I click on one of the incorrect averaged column cell, then click in the formula field and just hit enter, the correct value appears.

[bardy]

maybe the average for all three columns is actually the same? if you double click the box with the formula in it and it's highlighting the right cells then I don't know what to tell you. try changing the numbers in the columns to see if it has an impact on the top value.

[Cell Tech]

Commercial gasoline typically contains not only alkanes but also benzene, toluene, ethylbenzene, o-xylene, m-xylene, and p-xylene. These latter compounds are often added to boost the octane rating of the gasoline. Because refinery runs often vary in ratio of these components in a run, and because gasoline blenders often vary the amount of each component, the mole ratio of these compounds may serve as a “finger print” for the gasoline.

A large amount of gasoline has spilled into a creek in your town, and the spill is thought to have originated from one of several gasoline tank trucks that passed through the previous night. The authorities have alertly taken samples of the gasoline in the creek as well as the several gasoline tankers stopped at nearby truck stops, and they have brought aliquots of the samples to your mass spectroscopy lab. Can you determine the molar ratio of benzene, toluene, ethylbenzene, and the three xylenes in the samples by use of mass spectroscopy?

yes

Pure samples of the six compounds plus hexane are available to you, and you run mass spectra under identical conditions for each of the seven compounds. Because quantitative introduction of material into your mass spectrometer is difficult, you have (as is conventional in handling mass spectra) ratioed the intensities to the most intense peak (taken as 100 intensity units), and you do the same for the gasoline from sample from the creek. [Since this is just a homework problem we will not require you to analyze all 68 samples; analysis of one unknown will suffice to show your method.]

The mass spectra (normalized as described above) are in the file CHEM3472HWFG#9A-data-S2006xls. Assume that the unknown gasoline contains only these seven compounds.

Advice from your professor: This problem is not particularly difficult, if we just show you how to use the tools that are available to you.

First, set up the algebra:

NI(n, sample) = normalized intensity at m/e = n for a particular sample. In this case n runs from 27 to 107 although many of the intermediate values are zero.

x(compound) = mole fraction of the compound in the unknown. We will number the compounds as follows:

1 hexane
2 benzene
3 toluene
4 ethylbenzene
5 o-xylene
6 m-xylene
7 p-xylene


NI(n, unknown) = c  Σ x(i)NI(n,i) = Σ [c  x(i)]NI(n,i) Equation 1

where I runs from 1 to 7 and c is a constant which is chosen so that the maximum value of NI(n, unknown) is 100.0

Equation 1 is linear in the unknowns, the x(i), and we can use Excel and its capability to do multi-parameter least squares fitting [TOOLS-DATA ANALYSIS-REGRESSION-set intercept = 0], to find the x(i). The experimental values of NI(n, unknown) [n = 27-107] will be the y-block and the experimental values of NI(n,i) [n = 27-107, I = 1-7] will be the x-block. Once we have the values of c  x(i) calculated by Excel, we can calculate c [and the x(i)] by requiring that Σ x(i) = 1.

Now we go to the part that requires a good analytical chemist rather than just a “number cruncher”.

(a) In a least squares fit, one must always have more data points than parameters to be determined, so we must use data from at least 8 values of m/e.

(b) Not all values of m/e contain useful data. Many of the values are quite low, and since the NI data for the unknown are probably +/- 0.3, these values contain mostly noise. We will improve the validity of the fit by not using data from values of m/e where the data are dominated by noise.

Suggestion #1: Remove all rows for which the intensity in the unknown (which has a base peak of 100) is less than 5.

(c) Go through the NI data sets for the seven compounds and look for rows not to use in the fits and for rows that are vital to the fits. These will be values of m/e that distinguish compounds well. There will be no problem in identifying values of m/e that distinguish the alkane hexane from the aromatics, but find peaks that distinguish the four compound with the same molecular weight (ethyl benzene and the xylenes – Sounds to me like a great name for a chemistry-based 1950’s style singing group!) will be more difficult.

Suggestion #2: The mass spectra do a very poor job of distinguishing among the three xylenes. Remove the columns for o-xylene and m-xylene and fit for the “total xylene fraction”.

(d) There are mathematical algorithms for the choice of data to ******* (Principle Components Analysis is a well-known one), but you are to do this problem without recourse to those algorithms.

How to proceed (and learn):

1. Plot the mass spectrum for each component (hexane, benzene, toluene, ethylbenzene, and p-xylene). Excel will do this (somewhat). Select one column of data at a time. Use Chart Wizard-Column-Clustered Column. This works, but the abscissa is numbered by spread sheet row rather than m/e value. Compare these mass spectra visually, and select peaks that could be used for the analysis of the various components. This process is analogous to the FTIR/Raman material in HWFG#7.

2. Next, do a multiparameter regression of the data set in which the rows for which the intensity in the unknown is less than 5 have been removed and the columns for o-xylene and m-xylene have been removed. Now the fit should report the “total xylenes”. Be sure to check “constant is zero” in the Regression. Report the mole fraction of each of the “six components”. Describe your assessment of this fit in your report. Are your mole fractions accurate?

You may stop here and turn in the homework assignment for a maximum credit of 9 points out of 10.

If you want to carry out numerical experiments that will help you understand the perils of least squares fitting, then proceed

3. Do a multiparameter regression on the entire data set. Examine the estimates of the parameters. Do they look OK. Describe your assessment of this fit in your report. . Are your mole fractions accurate?


4. Do a multipararameter regression on the data set in which the rows for which the intensity in the unknown is less than 5 have been removed. Describe your assessment of this fit in your report. . Are your mole fractions accurate?


5 Do a multiparameter regression of the data set in which the columns for o-xylene and m-xylene have been removed. [This data set is a subset of the data set for part 1, not for part 2.] Now the fit should report the “total xylenes”. Describe your assessment of this fit in your report. . Are your mole fractions accurate?


What matters in creating a data set for which multiparameter regression works?

[wHATcOLOR]

hit F9 bro


edit: for andyslash

did it say "calculate" at the bottom before you did that?

[AndySlash]

I hit F9 and then 'Calculate' goes away.

But then when I pull the cell over to average again, it comes back.

[bardy]

can you just copy and paste the data (just values, no formulas) to another workbook and start over?

[wHATcOLOR]

when you hit F9, your formulas should update. F9 means "calculate"

by default, excel calculates automatically, but sometimes it gets set to manual, and it sounds like that is what you've got. it usually happens if you've got a bunch of data or a ton of formulas making it resource intensive to constantly recalculate

anyhow, to fix it, just go to tools, options, calculate, and click automatic. it might help to then save the file, close excel and open it again

[AndySlash]

It appears that is what happened. It was on manual. Changing it, saving, and reopening seems to have worked.

Thanks bo dude. And bardy for trying.

[AndySlash]

I just repped you so you'll have to wait for more.

[wally]

Jerome is the Excel king.