Office hacks: Excel hand mathematical model applications do not worry

Mathematical models can be used to analyze and solve many problems in the actual work, but many people mention “mathematics” on the headache, let alone the application. In fact, we see every day in Excel, has a variety of built-in analysis tools, without the need to go into the details of the internal mathematical model, you can make it work for us.

Find out Excel’s analysis tool library

In order to invoke the built-in mathematical modeling tools in Excel (this article takes the 2016 version as an example), we need to import a library of analytical tools. First of all, start Excel and create a new blank document, and then click the menu “File → Options” to open the Options dialog box, select the left side of the “Add-ins”, and then the right side of the “Management “set to” Excel Add-in “(Figure 1), click the” Go To “button, and then in the open Add-in macro window check the” Analysis Tool Library “and other items (Figure 2).


Figure 1 Excel 2016 options dialog box


Figure 2 available loading macros

Click “OK” to return, then switch to the “Data” tab, when you see the right side of the “Analysis” column shows the “Data Analysis When you see the “Data Analysis” item in the “Analysis” column on the right, it means that the loading is successful.

Describe the mathematical model and analysis of statistics

Statistical analysis of a given sample of data (or aggregate) to outline the summary characteristics of this data is known as descriptive statistics. In the following example, the data from a class of students’ language test scores are counted in order to understand how well the class has mastered the language course.

Start Excel and enter the raw data of the students’ test scores, here we assume that there are 66 students and the data is stored in the area A1 to A66. Click “Data Analysis” on the toolbar, select “Description Statistics” from the pop-up dialog box and click “OK”, then set the input and output areas in the new window that opens In the new window that opens, set the location of the input and output areas, check the items that need to be counted (Figure 3), and click “OK” to display the results soon (Figure 4).


Figure 3 Setting the description statistics option


Figure 4 Analytical results describing statistics

The analysis results are divided into two main categories, one is convergent information, which describes the central position of the distribution of the data set, such as mean, median, and plural. The other category is the variability information, which describes the discrete data of the data set, such as variance, minimum, maximum, kurtosis and skewness.

Behind each analysis result there is a corresponding mathematical model, such as the mathematical model of the mean (mod01.png), the mathematical model of the standard deviation (mod02.png), etc. We don’t need to understand the details behind these mathematical models, Excel has already done the calculation for us.

Quickly draw histogram models

Histogram as a way to present the mathematical model, it makes the data look more visual and intuitive.

Following the above example, in the data analysis window, select “Histogram”, OK to bring up the histogram dialog box, where the “input area” that is, the original data where, in this case, the A1 ~ A66 region. “Receiving area” can not be filled in, Excel will automatically fill in. “Output area” can choose any blank cells, in addition to check the “Plot”, “cumulative percentages” and “chart output “and other options (Figure 5). Click “OK”, you can get the histogram and the related statistical results (Figure 6).


Figure 5 Set the histogram options


Figure 6 Statistical results and histogram

Regression analysis and prediction

Regression Analysis is often used when we need to make predictions about the development of something. For example, whether there is a relationship between product sales and price and advertising, and whether sales can be predicted on the basis of known price and advertising, this model can be applied to the analysis.

First, collect past data and enter it into Excel. Next, open the data analysis window and select “Regression” (Figure 7).


Figure 7: Simulating sales data and invoking regression analysis tool

In the regression options dialog box, select the column “Y-value input area” for sales volume (A1:A8), which is the variable to be predicted in this case, also called the dependent variable. For the “X-value input area”, select the columns for price and advertising (B1:C8), which are the explanatory variables and are also called independent variables. Note that these columns must be adjacent to each other. The “output area” is set to a blank cell (e.g. A12). Finally, check the “sign”, “residual” and other items (Figure 8), click “OK” to get the detailed regression analysis results (Figure 9).


Figure 8 regression analysis parameter settings


Figure 9 Regression analysis results

In the regression statistics of this example, the correlation coefficient R value (Multiple R) is as high as more than 0.9, which indicates that sales have a very strong positive correlation with price and advertising. The R-squared coefficient (R Square) is 0.85, which means the model fits well (generally more than 70% is good).

More data, you can refer to the regression analysis-related books to learn to understand, this article is limited to space, not to explain one by one.


Leave a Comment