Statistics in Spreadsheets

Microsoft office contains multiple applications that altogether serve as a complete information management system. A spreadsheet is among those tools and is very important when the analysis of data is required. A spreadsheet is defined as a large sheet that contains data and information arranged in rows and columns. Statistical features of Spreadsheets are today a huge asset as many large multinational companies and research institutions use them for organization, categorization, and analysis of their financial data. There are multiple tools through which we can statistically analyze the data. Tools that facilitate working with statistics in spreadsheets are :

• Pre-Defined Mathematical functions like average, max, min, mean, mode, etc.
• 2D and 3D charts of various types like line charts, pie charts, bar charts, etc.

Mathematical Functions in Spreadsheets 

Microsoft Excel supports a wide range of statistical functions through which we can perform various operations like means, mode, average, etc of the given data. Some of the commonly used statistical functions are as follows:

Function Name	Formula	Description
AVERAGE(val1, [val2], …. )	AVERAGE(num1, [num2], …. )	This function is used to find the arithmetic mean or average of the cell in the given range
AVERAGEIF Function	AVERAGEIF(range, criteria, [avg_range])	This function is used to find the average of the cells that meet the given criteria.
MEDIAN Function	MEDIAN(num1, [num2], …)	This function is used to get the median of the given data.
MODE Function	MODE.SNGL(num1, [num2],…)	This function is used to find the most frequent value of the cell in the given range.
STANDARD DEVIATION Function	STDEV.P(num1, [num2], …)	This function is used to find how much the observed value deviated or varied from the average.
VARIANCE Function	VAR(num1, [num2], …)	This function is used to find the degree of variation in the given data.
QUARTILES Function	QUARTILE(arr, quart)	This function is used to divide the given data into four parts and can return minimum value, first quartile, second quartile, third quartile, and max value.
CORRELATION Function	CORREL(arr1, arr2)	This function is used to find the relationship between two variables. The range of correlation coefficient lies between -1 to +1.
MAX Function	MAX(num1, [num2], …)	This function is used to find the largest numeric value in the given data.
MIN Function	MIN(num1, [num2], …)	This function is used to find the smallest numeric value from the given data.
LARGE Function	LARGE(arr, x)	This function is used to find the nth largest value from the given data.
SMALL Function	SMALL(arr, x)	This function is used to find the nth smallest value from the given data.
COUNT Function	COUNT(val1, [val2], …)	This function is used to count the number of cells containing a number.
COUNTA Function	COUNTA(val1, [val2],…)	This function is used to count everything including numbers, error values, empty text, etc, present in the given data.
COUNTBLANK Function	COUNTBLANK(Srange)	This function is used to count only blank and empty cells.

Example:

In this example, we are finding the average of the following given data: 

Here, we use the AVERAGE(C2:C7) formula to find the average of 2nd test. Similarly, we can use other functions. 

Obtaining Charts in Spreadsheets 

We can also use charts to represent our statistical data in graphic form. Or we can say that a chart is basically a diagram through which we can plot information such that it is easier to perceive and analyze different relationships between data points. The spreadsheets allow us to embed charts of various types for better data visualization. 

For using charts one just needs to select the data cells that need to be plotted into a chart and then choose the chart of their choice. We can customize various properties related to charts such as the color, title of the chart, adjustment of size, etc. as per our requirements as well. The general instructions to create a chart for any given data are as follows:

• Select the data for which the chart has to be created.
• Go to INSERT > Recommended Charts. There a dialog box appears that contains recommended charts for given data as well as All Charts that are available.
• Select the one which you feel is most suitable for information and click OK. The chart will be presented in the worksheet area.

Type of Charts

The spreadsheet supports various types of charts:

1. Line Chart: A line chart that shows the variation of some data by sequentially connected points.

2. Bar Chart: This is another kind of Chart where data is represented in form of rectangular bars with height being proportional to the cell value. 

3. Scatter Plot: This plot is used to compare at least two sets of values of pair of data. 

4. Pie Chart: In a pie chart any information is presented as proportions to the whole data. For example – A person purchased 30 mangoes, 40 apples, and 10 bananas then a pie chart would present this information in the given fashion.

There are also many other kinds of charts besides the above that have different use cases such as hierarchy charts and pivot charts. Each and every one of these charts also supports 3D diagrams as well.