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?