MS Excel-based method for the preparation of target-oriented sampling plans

Friday, October 9, 2015

Authors: Zsuzsa Farkas, Kata Kerekes, J. István J. Szabó, Árpád Ambrus




For realistic evaluation of the results of control analyses, it is essential that it is performed in accordance with the purpose of the analysis. One of the prerequisites of the evaluation of the results using the usual statistical methods is random sampling, which means that each member of the predetermined population to be sampled (sampling frame) is chosen with the same probability. The sampling frame, in the case of stratified sampling, may include a subgroup of the whole population under investigation.

A single stratum can consist of, for example, production units deemed high risk, based on previous experience, businesses specializing in the production of organic products, or a certain age group of a country’s population.


Given that the analysis of all commercial lots is very rarely justified or feasible, inspection of production practice is performed on the basis of an analytical program based on random sampling. In this paper, an MS Excel-based procedure satisfying special conditions and suitable for random sampling is presented, which was developed for the inspection of the aflatoxin M1 (AFM1) contamination of milk samples, however, its principles – adapted to the given conditions – may be applied in other cases as well. One of the advantages of the procedure developed is that it enables the creation of sampling sequences with significantly higher numbers, than do manual methods. Performing the operation helps in all cases when a fixed number of random samples is taken regularly from a sampling frame of constant element number.

For example, the procedure may be suitable for the monitoring of the production of a given group of raw material suppliers, whether it be the inspection of the pesticide residue content of raw materials used for the production of dairy products, baby food, or other fruit or vegetable-based products, during the harvest or immediately prior to it. Another important advantage of the method is that it enables the weighting of the units within the sampling range.


The goal of the statistical analysis (Farkas et al., 2014) providing the background for the procedure – which was carried out together with Italian partners – was to prepare a sampling plan that enables the detection of an increase in AFM1 concentrations due to environemental effects in a timely and cost-effective manner (with a minimum number of samples and a given statistical reliability). Using the sampling plan, and by intervening immediately, destruction of large lots with AFM1 contaminations exceeding the limit value, or their commercial distribution can be prevented.

 read more ...