Thank you NeoPa

I really wasn't expecting this to be so difficult.

For those that

__REALLY__ want to know more about what this thread is a step to, what I am doing is an analytical method call normalization of area response against known value,

(Not to be confused with database normalization :) ), often used in chromatography to convert raw percent peak areas into something more useful such as the percent mass of a compound found in the sample as it relates to a reference compound within the sample.

In Excel I start out with the known sample/reference

+ In first row the known values in the cells

A1, C1, D1, F1, H1

(in the raw responses we have A:H so the component names will be [A], [b], [C] ... [H])

So in the following anytime you see [A] that's the component

If you see just (letter) or (letter)(Number) that's a cell reference. [x]=one of the [A]-[H]

The reason I don't have a B1, E1, G1 known value is that in the end we use Const([A]) for both [A] and [b] and the same for [E] and [G]... I'll show that at the end :)

+ In the second through 4th rows the raw instrument values from the triplicate analysis through the instrument for the known. This tells us how the instrument is responding to the standard. (an aside, I've gone as high as 30 analysis of the known reference standard to establish response... see below)

+ Fifth row calculate the averages for each... however we group the value for A+B, D+E, and F+G:

A5=Avg((A2+B2),(A3+B3),(A4+B4)), C5=Avg(C1:C3), D5=Avg((D2+E2)...),...

+ Sixth row calculate the RF([x])

A6 = A1/A5, C6 = C1/C5, D6 = D1/D5 ....

Notice there's no RF([b]) because it's included in RF([A]) same for [E], and [G] (I'm getting to that :) )

+ Seventh row calculate the Const([x]) (no RF([b]) no Const([b]) same for [E] and [F]...

A7= A6/$F$6, C7= C6/$F$6, D7 = D6/$F$6 ....

obviously, because I'm using $F$6 when we come to

Const([F]) = F7 = F6/$F$6 = 1

Because [F] is the reference point that all of the other responses are being normalized (scaled to) against.

The seventh row are the Const([x]) that is then used against the unknown sample raw results to convert the raw values to the final result by standardizing (normalizing) the raw response. This is the number, Const([x]), I enter into the instrument software and that software does the following final calculations on the unknown for us... yea!

+ Sometimes we like to verify that the software is doing the calculations correctly for unknown samples.

So A10:H12 are used for the raw instrument response...

So very much like the reference sample the Column A+B etc... are added, however, this time instead of the average we multiply the corresponding value from row 7

+Normalized result:

Is the raw response multiplied by the corresponding Const([x])

So for just

__one result __say row 10

A20 = A10 * A7

B20 = B10 * A7 (here's that combined Const([A+B])

C20 = C10 * C7

D20 = D10 * D7

E20 = E10 * D7

F20 = F10 * F7

G20 = G10 * F7

H20 = H10 * H7

I20 = sum(A20:H20) (used to rescale)

Do this for each analytical result (if three runs then we have rows 20 through 22 like this)

To find that final answer

Say drop to row 30 then for the

__first result __we would have

[A] A30 = A20/$I$20

[b] B30 = B20/$I$20

[C] C30 = C20/$I$20

[D] D30 = D20/$I$20

[E] E30 = E20/$I$20

[F] F30 = F20/$I$20

[G] G30 = G20/$I$20

[H] H30 = H20/$I$20

and so on in the following rows for each result

And for the final report we have - if we ran in triplicate:

[A+B] = A35 = Avg(sum(A30,B30),sum(A31,B31),sum(A32,B32)...)

[C] = C35 = Avg(C30:C31...)

[D+E] = D35 = Avg(sum(D30,E30),sum(D31,E31),sum(D32,E32)...)

[F+G] = F35 = Avg(sum(F30,G30),sum(F31,G31),sum(F32,G32)...)

[H] = H35 = Avg(H30:H31...)

(the "..." is if I have more than three analytical runs, I'll do as many as 30 or 40 analysis against an unknown (or the known for that fact) when doing validations, co-labs, secondary standards, etc... in statistics that 30 is an arbitrary magic number and I am assuming a normal distribution

Sampling Distribution of the sample (KhanAcademy))

Of course this become much more interesting when running both the reference and then unknown on multiple instruments and for different reference standards....

(so, on a given day, run of 30 injections on each instrument (4 in my case) gives me 120 results. I can then look at the individual groups of 30 from each instrument and with ANOVA or T-Test each instrument should be reporting the "same" value (yes simplified explanation) which if they are all in agreement then I can then take the 120 results and use those numbers to give me the population mean, and the mean and the median should be the same and if not then I have other issues, calculate the intra-lab error and other interesting statistical information. If I do this over several days, then I have a larger set of data which allows me to see how the lab accuracy and precession is holding from day to day - I don't normally run a sample 30 times on a daily basis - I'd never get any other work done!)

Nice thing here is that for the software that runs the instruments we "calibrate" by calculating the Const([x]) and the underlying Oracle database does the calculation against the unknown, that is to say

We calculate A7:F7 and enter these into the software

The Software then does the row 10 through 30 for us... I could have it do the row 35; however, we need to if [b], [E], and [G] are also being detected so we do that final calculation by hand.

ok, time for an ice-bath for the brain.