Custom field for spiked and unspiked samples

Options
JustAChemist
JustAChemist
in Software Products

What we do is run two injections of a sample without spike and two injections on the spiked sample via G.C.. We then have three reference samples, a blank, a reference, and the spiking solution which we run two injections on each. In Excel we have the following equation

amnt = (600 x 1.5 x ( s - b ) x ( u - b )) / (10.5 x ( r - b ) x ( g - u ))

Where s = avg(spiking solution peak area); b = avg(blank peak area); u = avg("as is sample" peak area); r = avg(reference peak area); and g = avg("spiked sample" peak area) for the peak of interest. The 600, 1.5, and 10.5 are concentration, sample mass, and dilution and then the ratios give us basically a simplified method of known addition.

A typical sample sequence would be (I've abbreviated a few things :) ):

  • Row  Vial   Sample Name         Label       #Inj     Method…   Function
    1       1       Reference                r              2        Con...          Inj. Samples
    2       2       Blank                       b              2        Con...          Inj. Samples
    3       3       Spike                       s              2        Con...          Inj. Samples
    4       4       Sample 1                U001        2        Con...          Inj. Samples
    5       5       Sample 1 spiked    G001        2        Con...          Inj. Samples
    6       6       Sample 2                U002        2        Con...          Inj. Samples
    7       7       Sample 2 spiked    G002        2        Con...          Inj. Samples
    ---
    N      ---      ---                            ---           ---       ---        Summarize Custom Fields
    N+1 ---      ---                            ---           ---       ---         ConstituentRprt  Report

Custom calculations are currently:

NullToZero = REPLACE(Area,0)  (occasionally there is no response in the blank)

Ravg = r..%.AVE(Constituent[Area])

Bavg = b..%.AVE(NullToZero )

Savg = s..%.AVE(Constituent[Area])

This is where I get stuck

Uavg = u%..%.AVE(Constituent[Area])

Gavg = g%..%.AVE(Constituent[Area])

what we need is to somehow use the Ravg, Bavg, and Savg values and then have the

amnt = (600*1.5*(Savg-Bavg)*(Uavg-Bavg))/(10.5*(Ravg-Bavg)*(Gavg-Uavg))

to work such that:

for “u001” and “g001” we would get

Uavg = AVE(Constituent[Area of U001],Constituent[Area of U002]) and the same with G% so that we have:

amnt for Sample 1 = (600*1.5*(Savg-Bavg)*(Uavg(u001)-Bavg))/(10.5*(Ravg-Bavg)*(Gavg(g001)-Uavg(u001)))

similarly for “u002” and “g002” (using the same values for Savg, Bavg, Ravg - only running these samples once in the sequence)

amnt for Sample 2 = (600*1.5*(Savg-Bavg)*(Uavg(u002)-Bavg))/(10.5*(Ravg-Bavg)*(Gavg(g002)-Uavg(u002)))

etc….

I just cannot see creating unique custom fields for 20+ samples (i.e. Gavg = g1..%.AVE(Constituent[Height]); Gavg = g2..%.AVE(Constituent[Height]), etc...) that would be some 40 unique custom calculations and if we have to run more samples then another set of equations (YUCK)...

I have tried to use "Summarize Custom Fields Incrementally" function between rows 5 and 6 and again between 7 and 8 etc for each spiked and unspiked sample pairs; however, that doesn't quite work and I am not able to use wild cards in the [Ref Label] nor more than one sample label in the [Ref Label] column when using the S.C.F.I. function (I can do this with the calibration function - sigh).

If I use multiple S.C.F.I. to bracket the as is and spiked samples and reference the U001, G001, U002, G002 etc...then with each iteration the areas accumulate and the final calculation result is wrong

I can get the Ravg, Savg, and Bavg to work correctly using either the "Summarize Custom Fields" or the "Summarize Custom Fields Incrementally" functions either at the end of the sequence or inserted withing the sequence.

Any help pointing me in the right direction is greatly appreciated
-Just A Chemist

Comments

  • cemery
    cemery
    Options
    Thank you for your question. Due to its, if you are still having issues,  please contact iRequest Technical Service/ Support:  http://www.waters.com/waters/iRequest-Technical-Support/nav.htm?cid=10006027
  • MJS
    MJS
    Options
    @JustAChemist

    I see this is a bit old, but have you found a resolution?  My thoughts on this one is that the relative reference is the trickiest part.  I've had similar situations, but this is by far more complex.  I think that the way I might pursue it would be to utilize an ENUM approach with the Use As set to field and having the various 20+ formulae copied into the translation table rather than having the 20+ actually set as individual custom fields.

    So, something like the following perhaps:
    Peak/ENUM/Calculated, Result Set Only, Use As=Field

    ENUM(EQ(Label, "G001"),  EQ(Label, "G002"), EQ(Label, "G003"), EQ(Label, "G004")).....repeating on for 20+ G### labels.

    In the translation table, you'd then have direct custom formulae...
    Value 0=(600*1.5*(Savg-Bavg)*(Uavg(U001)-Bavg))/(10.5*(Ravg-Bavg)*(Gavg(G001)-Uavg(U001))) (fc)
    Value 1=(600*1.5*(Savg-Bavg)*(Uavg(U002)-Bavg))/(10.5*(Ravg-Bavg)*(Gavg(G002)-Uavg(U002))) (fc)
    and so on...

    I don't see any way around the repetitive fields, but at least using the ENUM approach, you really only have 1 custom field to worry about reporting and it simplifies the processing demand as it will only calculate what matters based on the labeling rather than 20+ that don't apply when Empower tries to calculate all the others that aren't applicable all the time (should be a massive processing savings).
  • JustAChemist
    JustAChemist
    Options

    Thank You MJS

    I haven't found a workable solution to this situation and unfortunately this project was placed on hold. 

    There was another solution offered that I didn't get a chance to attempt before it was removed from the forum and I didn't save it down - I didn't know that the moderators actually delete such things from the threads... that is most unfortunate for the forum members.

    I'll have to take a look at the enumerated concept. Still a bit of a kludge given that I now have an Access Database that will handle this with ease - and only one set of equations.

Categories