How do I average across SampleTypes using Excel Template reports?

In previous adventures I have tried using the IronPython reporting process to create reports that average the retention time, response, and peak width for system suitability injections. I made some progress with the python-based reporting process but in attempt to simplify I found more success following along with G3335-90159 which uses the Excel MassHunter Plugin in conjunction with customizable Excel templates. Despite this progress, I've run into an issue that I don't understand.

Firstly, I am using Quantitative Analysis Version B.08.00 / Build 8.0.598.0 to process data and generate reports. The report method I'm currently using runs an Excel template modified from the default QuantReport_ESTD_ResultsByCompound_B_06_00.xltx file included in MassHunter/Report Templates/Quant/en-us/Letter/ESTD/Results_NoGraphics.

The modifications I have made from the default file include unhiding the SampleType column, applying a filter via the MASSHUNTER REPORTING -> Advanced Properties tab for "ResponseCheck" SampleTypes, and adding a formula column intending to average the retention time corresponding to compounds observed in ResponseCheck samples.

The formula that I use in the formula column is "=AVERAGEIFS(H:H, G:G, [@Compound])" which appears to average across all sample types per the second image included in this post: note that the average is less than any of the retention times.

My first thought was to modify the formula to "=AVERAGEIFS(H:H, G:G, [@Compound], C:C, [@SampleType])" however this results in a #DIV/0! error when I generate the report. In the absence of understanding what's going on under the hood, I would expect this to instead give me an average of any retention time that satisfies both the @Compound and @SampleType criteria.

More confusingly, each individual @SampleType is printed (See Figure 2).

My intent is to calculate percent RSD for the retention time, peak area response, and ion ratio for all compounds in each system suitability injection. Am I on the right track with this or is there a better way?

Parents
  • Hello Woodsja,

    Please try:
    =AVERAGEIFS([RT],[CompoundID],[@CompoundID],[PeakID],0,[SampleType],"ResponseCheck")

    Average the RT when:
    The compound is the current compound and not the others in the quantitation method.
    The peak is the primary peak and not the alternative peaks detected in the extraction window.
    The sample type is ResponseCheck

    Note that if no peak is detected, then that entry will not be averaged as there is no PeakID. If you wish to average a
    0 for not detected peaks, then I would suggest using a SUMIFS and set up a counter and then perform the average
    calculation with those two variables.

    When the report is processed, Excel makes one spreadsheet which contains all the peaks integrated in the extraction
    window for all the compounds for all the samples. Thus one must insure that the filters or criteria in the AverageIFs formula
    work on the primary peak for the current compound - and then average across the desired samples. Even if a field is
    hidden, it is typically still present in the intermediate spreadsheet so one must design the filter/criteria with that in mind.

    David

    www.agilent.com/.../page

Reply
  • Hello Woodsja,

    Please try:
    =AVERAGEIFS([RT],[CompoundID],[@CompoundID],[PeakID],0,[SampleType],"ResponseCheck")

    Average the RT when:
    The compound is the current compound and not the others in the quantitation method.
    The peak is the primary peak and not the alternative peaks detected in the extraction window.
    The sample type is ResponseCheck

    Note that if no peak is detected, then that entry will not be averaged as there is no PeakID. If you wish to average a
    0 for not detected peaks, then I would suggest using a SUMIFS and set up a counter and then perform the average
    calculation with those two variables.

    When the report is processed, Excel makes one spreadsheet which contains all the peaks integrated in the extraction
    window for all the compounds for all the samples. Thus one must insure that the filters or criteria in the AverageIFs formula
    work on the primary peak for the current compound - and then average across the desired samples. Even if a field is
    hidden, it is typically still present in the intermediate spreadsheet so one must design the filter/criteria with that in mind.

    David

    www.agilent.com/.../page

Children
No Data
Was this helpful?