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?

  • Hello Woodsja,

    An intermediate step in the reporting process is the creation of a spreadsheet which contains all of the peaks in the extraction window for a compound for all of the compounds for all of the data files. The formula must insure that it is working on a single peak in the extraction window, on the current compound, and for your case, for all sample types ResponseCheck. Please try the following: 

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

    Average RT when

    Compound is the current compound

    PeakID = 0 - the primary peak and not alternative peaks in the extraction window

    The sample type is responsecheck. 

    Be aware that if there is no primary peak detected, then the average command will not include that entry. If you wish to include a 0 for not-detected peaks, then use the sumifs command to form a sum and create a count variable to keep track of the number and calculate the average separately. 

    For the repetition, you might be able to insert a filter of Top = 1 on the Sample field using Advanced Properties; however, doing so may result in just a single row being printed. 

     If you wish, you could contact us at:

    https://www.agilent.com/en/contact-us/page

    and be put in contact with me and I could provide some examples. Yet I think you'll arrive at the answer with the information above. David Presser

  • 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

  • Hi Woodsja,

    I just wanted to follow-up to see if your question has been answered. If so, please come back to the post, let us know what you found, and click Verify Answer on the response so it will make the solution more visible.  If you still need help, just let us know and we would be happy to continue working with you.

Was this helpful?