XLSTAT: Data Statistics (Mean, Median, Standard Deviation Etc.) in Excel

 

Dataset for Example

The attached data summarize a survey on monthly online shopping spending. Each row represents a respondent, with columns for their spending and age group.

Our objective is to analyze spending by age group using descriptive statistics:

1. Mean and median to measure central tendency

2. Standard deviation, variance, and coefficient of variation to assess dispersion

This analysis will help identify key insights and differences across age groups.

 

Setting Up

• Open XLSTAT.

• Click on the Describing data menu and select the Descriptive statistics feature.

  

• The  Descriptive Statistics dialog box appears.

In the General tab:

• Select the column corresponding to the money spend on online shopping in the Quantitative data field.

• Select the column corresponding to the age class in the Subsamples field.

• We also want to display Variable-Category labels in the output. These include the variable name as a prefix and the category name as a suffix.

• Select the Sheet option in order to display the results on a new sheet and the Sample labels to consider the first row of the data table as labels.

In the Options tab:

• Activate the following options:

• In this tutorial, we focus on descriptive statistics so we do not activate Charts.

• Since we are only dealing with one single variable we do not choose the Normalize or Rescale option. That is more interesting in the case of several variables measured on different scales.

In the Outputs tab:

• Click the All button to select all the statistics for quantitative data.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

A full set of descriptive statistics is displayed for all respondents (column B) as well as per age class (columns C-E).

The most commonly used basic statistics are the measures of the central tendency (e.g. mean, median) that give information around the center of the data, and the measures of dispersion (e.g. standard deviation, range, variance, variation coefficient), that describe data variability.

Definitions and formulas can be found in the XLSTAT Help menu (click on Help button in the dialog box).

a. Interpreting the central tendency

The mean shows that respondents globally spend 156 USD on average per month in online shopping.

The median value is 139 USD. This means that half of the respondents spend more than 139 USD and the other half less than 139 USD. The data is likely to be skewed to the right since the median is slightly lower than the mean value.

Looking across the three age groups, we observe that consumers aged between 30 and 45 spend on average more than the two other groups (mean and median). One interpretation could be that people of this age category have more money to spend than younger categories while they are keener on technology than the older classes.

b. Interpreting the dispersion

Although young consumers seem to spend more on average than the oldest ones (>45), they have a higher sample standard deviation (SD = 62). This means there is a wider range of amounts spent by category (30-45) compared to category (>45). Similarly, we may say that older people tend to spend similar amounts of money (low SD). Different explanations can be given. For example, we might say that young buyers include more diverse socio-professional categories (students, professionals, unemployed) than older ones.

These conclusions can be also drawn based on the variance. The benefit of using the SD instead of the variance is that SD uses the units of the original scale.

Another measure of spread is the variation coefficient. This measure is particularly interesting to use when comparing samples with different units because it is unitless. The higher the variation coefficient , the greater the level of dispersion around the average.

Standard Deviation (n) or Standard Deviation (n-1)?

In this example, we used the 150 respondents as a sample in order to study the whole population. 

If we could collect data for all people who shop online (which is not realistic), the  population SD [Standard Deviation (n)] or population variance [variance (n)]  would be more appropriate.