Research on Calculation Method of Mean in Sports Statistical Data with EXCEL

,

number of observations, and is only called the arithmetic mean when it is distinguished from the harmonic mean, the geometric mean, etc. It's advantage is that representation is easy to accept, all values are used, all values contribute to representation, there is always one and only one, not as there are many as there are modes .It's disadvantage is that it is easily influenced by the extreme value at both ends, which weakens its representativeness [2] . It's usually represented by an ' x 'or an 'M'. The formula is:

The Classification of Arithmetic Mean
In the practical activities, according to whether we often consider the effect of extreme value on the mean, we divide the arithmetic mean into the normal mean (mean) and the trim mean (also known as the internal mean) , and the trim mean into the symmetric trim mean and the asymmetric trim mean with taking out the different extreme values on both end.

The Calculation Methods of the Mean in EXCEL
Excel is an important component of Microsoft Office, which provides many common data processing functions, including a large number of formula functions, various graphical functions, powerful statistical analysis of data, etc. [3] In this paper, we take the 10 referees' scores in the kindergarten gymnastics competition of normal education department of our college as an example (see Table 1), and explain how to calculate various mean in EXCEL. There are many built-in functions in EXCEL that we can use AVERAGE ( ) to calculate the mean, or we can use DAVERAGE ( ) or SUMIF/COUNTIF to calculate the conditional mean. When using a function, we have two functions inputting ways in Excel, one is to directly input the function formula content, if you can enter "=AVERAGE (B2: B11)" in the B12 cell, you will calculate the mean (see Table 2). Another is to use "Insertion function" dialog box, then complete the calculation according to clew. [4]

Using the pivot table to calculate the mean
For cases where two conditions must be averaged at the same time and the number of persons needs to be averaged together, the easiest way to handle this is to use data pivot table to establish cross list. Data pivot tables are interactive tables that quickly summarize and cross-list large amounts of data by rotating their rows or columns to see different summaries of the source data, and by displaying different row labels to filter the data, which shows the concrete manifestation of Excel's powerful data processing ability. In the application of Pivot table for data summary, we should open the "Pivot Table and Pivot Chart Report" function in the "Data" menu, pressing the "Pivot Table and Pivot Chart Wizard" to finish step by step. [5] While accessing cross-analysis table, we can rationalize the various parts of the pivot table according to the research needs to determine the row, column, field and data content. What should be noted is that if the pre-selected calculation sum does not meet the study requirements, you may change the summary way by double-click "Sum" to select the appropriate calculation options. Summary methods include sum, count, mean, maximum, minimum value, product, value count, standard deviation, population standard deviation, variance, population variance and so on, this research summary way chooses mean.

Using "descriptive statistics" in "data analysis" to calculate the mean
Excel provides a set of data analysis tools, called DAD (Data Analysis Database). Procedures can be saved in the establishment of complex statistical analysis, since we only to provide the necessary data and parameters for each analysis tool, then the tool will use appropriate statistics, and the output table will show the corresponding results. Some of these tools can also generate charts as they generate the output table. Using add-ins and analysis Tool Pak installed, you can get "Data Analysis" in "Tools" menu. By using "descriptive statistics" in "data analysis", we can simultaneously obtain the statistics of multiple groups of data in the selected area, such as arithmetic mean, median, mode, standard deviation, variance, extreme value, etc.
In this study, We describe the statistics dialog box in the "input area" to set the data area to be processed (B1: E11) ,select "column by column" in the "grouping mode" , click the "flag in the first row" , set the output area in H 1 position of the current work table , click on "the summary Statistics" , choose confidence level of the mean as "95%" , the setting good need to display the most value number, points out "certainly"

International Journal of Computer and Communication Engineering
then (see Table 3).

Calculation of Trim Mean 3.2.1. Calculation of the symmetrical trim mean
The symmetrical trim mean refers to the average of an array after removing the same numbers extreme value in the head and tail, which is in order to eliminate the extreme's impact on the mean. The calculation of the trimmed mean is also by the TRIMMEAN function. The grammar of function is TRIMMEAN (array, percent), in which the "Array" refers to the data array or the data field , and "Percent" refers to the percent of the removed extreme, which is the multiple of 2 to remove same numbers of extreme in both ends [6]. In this example, the 1 highest score and the 1 lowest score should be removed from each of the 10 judges, the percent is: Percent=2/10=0.2, we can input it in the corresponding in cell B12: "=TRIMMEAN(B2:B11,0.2)", then the symmetrical trimmed mean will be gotten (see Table 4).

Calculation of the asymmetrical trim mean
In sports practice, we sometimes encounter another complex situation, such as in a set of data in the head and tail after removing different numbers of extreme value to calculate the mean. This situation is called asymmetric trim mean, and it can not be simply achieved through a statistical function, the following is the discussion of several methods to achieve.

Nested function method
A nested function is a function that the researcher may need to use as an argument to another function under certain circumstances. When dealing with a complex problem, the use of one or two functions alone can not effectively solve the problem, or even can not solve it, the problem can be easily solved by using nested functions. [7] For this problem, it is easy to realize with nested function method, and train of thought is clear: to calculate the sum of all the data, remove the need to eliminate the different number of maximum and minimum, and then divided by the number of remaining data, then we can take the asymmetrical trim mean. We will only need to use a function or formula language expression for this process. In this example, we want to get rid of one maximum and two minimum, and input the following formula in cell B12: =(SUM(B2:B11)-LARGE(B2:B11,1)-SMALL(B2:B11,1)-SMALL(B2:B11,2))/ (COUNT(B2:B11)-3) (see Table 5).

Using VBA self-built function
The above nested function is suitable for cases with few numbers of data. If the competition had dozens of classes or 200 tables to process, we would have to do the same thing many times. This competition is to remove one maximum score and the corresponding two minimum points, and the future competition to remove a number of maximum score and minimum points, or the number of referee are different, and need to repeat the input formula to complete, which is relatively cumbersome.
Excel also has a powerful function -VBA. VBA Technology (Visual Basic for Applications) is a programming language developed by Microsoft to perform automated tasks in its desktop Applications. VBA programming in Excel does not require the installation of any other proprietary programming language development platform , it is a macro programming language of Visual Basic, which can make full use of the functions contained in the Excel itself to program, reduce the complexity of programming, can achieve automation, batch processing tasks program. [8]- [10] According to their own needs, the researcher can write the function through the VBA, and it can be ported to other EXCEL files or any computer.
In this study, assuming that the function to achieve the above function is named "mean_way ", the following code can be written in VBA to calculate the asymmetrical trim mean. After opening the VBA editor window, right-click on This work book in the Engineering File Explorer Window, select insert module, and enter the following code in the inserted module: The function is defined as "mean_way " and ran is the region of the data being processed, in this case the region is (B2: B11), La is the maximum number of numbers to be removed, in this case the maximum number is 1, sm is the minimum number of numbers to be removed, and in this case the minimum number is 2.
Function mean_way (ran As Range, la As Integer, sm As Integer) As Variant Dim sum, i, j, count As Variant 'Find the sum of all the data Sum = Application. Work sheet Function. Sum (ran) 'Subtract minimums of sm For i = 1 to sm Step 1 Sum = sum -Application .Work sheet Function. Small (ran, i) Next 'Subtract maximums of la For i = 1 to la Step 1 Sum = sum -Application. Work sheet Function. Large (ran, i) Next 'Find the number of data left after the minimum and maximum number is removed Count = Application. Work sheet Function. count (ran) -la -sm 'Calculate the mean mean_way = sum / count End Function After completing the code input and returning to the EXCEL table, using the self-built function "mean_way " in Cell B12, you can calculate the specific value of the mean of the asymmetric trim mean. If you want to use the built in function on other EXCEL files or computers, you can export the edited module file, copy it, and import the module file into the desired EXCEL file.

Conclusion
Although Excel is not classified as statistical software, and its statistical functions can not be compared

International Journal of Computer and Communication Engineering
with professional statistical software such as SPSS and SAS, but it is a data statistics tool with a collection of data table, work function, VAB applications and report processing, which can not only deal with figures, but also with graph and character. The data in Excel can be shared with and converted to those in Word and SPSS and any other databases. Therefore, it is necessary to further explore the data statistical processing method of EXCEL, study the expansion software of EXCEL, comprehensively improve the data statistical function of EXCEL software, and better serve sports practice.