# Custom field for spiked and unspiked samples

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

## Comments

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

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.