Advanced Excel Statistical Function Cheat Sheet for Data Analysis | Video example

Excel has a large range of Statistical Functions, that perform calculations. Excel Statistical function is a subset of Mathematical function. So, we can say that all statistical function in excel is a mathematical function in excel. In this tutorial, I will show you advanced excel statistical function cheat sheet. It will help you to perform data analysis in excel.

Excel Statistical Function

This excel statistical function tutorial, you will get the best excel statistical formula with an example. In my previous post, already explain the best daily use ms excel formula list. So, for advanced excel tutorial, check excel VBA programming tutorial for beginner.

What is a statistical function in excel?

Statistical function in excel is part of maths function in excel. So, excel Statistical functions are excel mathematical functions, but not all math are statistical functions.

A math function is fundamentally designed to convert a set of one or more digits into a result. It includes the addition or square roots, or more complex excel formula list. But, statistical function converting data into information packet that adds extra interest. It helps understand distributions, calculate the average, or produce other outcomes that help us realise how a bunch of numbers might be placed out.

Mostly, statistical function is used to predict the future or design a trend. For example, you can apply statistical formulas and calculate most selling laptop in World.

Excel Statistical Function Cheat Sheet

Advanced excel cheat sheet

Median

A common statistical major is called as the median. It calculated middle of value in the range of value. Let’s go with Excel median function with example.

In above video suppose I click on salary column, the bottom of indicate average, sum, the minimum and maximum value of salary column.

You can change bottom statistics variable any time.

To change bottom statistics in excel sheet –

1) Click on right click on green line at the end of excel sheet

2) Select or deselect items from customising status bar.

Median statistical function working with example

Syntax – =Median(range)

Median is excel statistical function which calculates the middle value of selected area. It is different than average function and calculates the middle value between a specified range. If specified range is even then, it calculates the average of middle two column.

MODE

The another function not exactly related to a Median function, but use commonly to calculate basic of statistics major is called MODE.

It simply tell us, which value of most occurred in given range.

MODE formula with a Video example.

Syntax – Mode(Range)

This function is available for compatibility with excel 2007 and earlier version. It used to return the most frequently occurring or repetitive value in given array.

Read- Vlookup video tutorial for an expert.

Rank, Rank.EQ or Rank.Avg

Rank is another function, which is compatibility with excel 2007 and the previous version. It is used find out the rank of a particular number compare with the list of value.

Rank and Rank.EQ both work same.

Rank Formula with an example.

Syntax –

Rank(number, Range)

Rank.Eq(number, Range)

Rank.Avg(number, Range)

So, the rank function calculates, the rank of specified number comparing with supplied range. If two number are a tie, then it shows the same rank for both numbers. In contrast Rank.Avg, If two number has the same value then it will assign an average rank of both numbers.

Large and Small

Large and small are two more excel statistical function, which is handy to find out largest and smallest value. Max and min function gives you maximum and minimum value within the list. But, suppose you want to calculate second maximum value or third maximum value, then you need to sort the list and find the value.

Thier is a function large which will give you largest value in given list. It is used to return kth largest value from the data set.

Large excel formula

Syntax – Large (array, number)

Here, array indicates list and number indicate which largest number.

So, If you want to find out 4th largest value from an array, then your large function must be large (array, 4).

Small Excel Formula

Like large, the small function will give you smallest value in given list. It is used to return kth smallest value from the data set.

Syntax – Small (array,number)

So, large and small both occasionally handy to get kth value. It is one tool that gives you fastest way data instead of sorting of data.

Example – small(array,5) – It will calculate the 5th smallest value in the array.

Read-Important keyboard shortcuts on excel sheet.

Conclusion

These are common function frequently used to data analysis in excel sheet. It will reduce your efforts and speed up data analysis process. Syntax and function are not hard but use properly and act as advanced excel expert. I think excel statistics function will help you in your daily excel works. If any difficulty please comment below.

Leave a Comment