MS-Excel is an important & most commonly used software tool to perform simple mathematical functions, visualization like plots & graphs as well as advanced excel functions for statistical techniques for data analysis. It has come a long way when first introduced in 1987 by Microsoft as a pioneer spreadsheet software. Since then many new functions are introduced & capabilities are also increased of this software.
On this page we will discuss about some important functions which are used by statisticians & researches everyday. We will also look into some advanced excel functions like pivot tables, data validation, & other analysis functions which are recently introduced in Excel-2016.
I assume that you are aware of simple drag & drop methods & special short keys & skipping the same for now.
All excel formulas can be written in cells where it is being applied or after selecting the cell we can write the same after an '=' sign in formula bar.
First let's begin with simple mathematical functions.
Arithmetic functions: All arithmetic functions can be performed in excel by just selecting the values. some of the functions are given below.
SUM function: This is used to sum all the selected cells. First cell & last selected cells will appear in the formula i.e.
= SUM(Cm:Cn)
Average function: It is used to find arithmetic average of selected cells value. This is similar to arithmetic mean. for other kinds of means like Geometric or Harmonic mean excel have different formula in Excel-2016
There are other functions like AVERAGEA or AVERAGEIF that combines power of logic with calculation. AVERAGEA is similar to AVERAGE function, only it consider a text string mentioned in selected cells as 0 or 1, depending upon the argument provided. AVERAGEIF or AVERAGEIFS test for logical conditions applied to particular cells & if it tests TRUE, then only corresponding Average_range cells is considered in calculation.
COUNT Function: As name suggest it counts no. of cells, based on some criteria attached to it. There are several count functions in this family, COUNT( ) counts for cells that has numbers in them. While COUNTA() counts for cells that are not empty. Some other COUNT functions are listed in table below with their usage.
| Function | Explanation |
| COUNT | Counts for no. of cells that has numbers in them |
| COUNTA | Counts for all non-empty cells |
| COUNTBLANK | Counts for all blank cells |
| COUNTIF | Counts no. of cells with in a range that meets the condition |
| COUNTIFS | Counts no. of cells with in a range that meets the set of condition |
| DCOUNT | Counts no. of cells that contains number in a database |
| DCOUNTA | Counts no. of cells that are non-empty |
Statistical Functions: Excel has lots of functions that are used extensively by data scientist when analysing & applying various hypothesis tests, & calculating various statistics of a data. Excel 2016 has special portion under Data tab, which has almost all the statistical techniques. I will explain each & everyone of it in detail with their utility & approach.
Path: Data -> Analyze -> Data Analysis
 |
| Pic3: F-Test- 2 sample variance Analysis for Height & Weight of 5 student |
