Tag

## STATISTICAL FUNCTIONS

Browsing

The Excel PERCENTRANK.INC Function calculates the relative position between 0 and 1 inclusive,within a supplied array.In MS Excel PERCENTRANK.INC is categorized under statistical function.

## Purpose of Excel PERCENTRANK.INC Function

To get the relative standing of a value within a data set. For example, you can use PERCENTRANK.INC to evaluate the standing of an aptitude test score among all scores for the test.

## Return value

PERCENTRANK.INC returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.

## Syntax

= PERCENTRANK.INC(array,x,[significance])

## Usage notes

• N/A error will Occur if the supplied value of x is smaller than the minimum, or greater than the maximum value in the supplied array.
• NUM!error will occur if the supplied [significance] value is < 1.
• NUM!error will occur if the supplied array is empty.

### Availability

Since 2010 for Windows.
Since 2011 for Mac.

The Excel VAR.P Function is used to compute the variance for the population. Variance is a statistical measure that is used to find out the amount that value differs from its average value. In Excel, the VAR.P function is categorized under statistical function. This function ignores logical values and text in the population.

## Purpose of Excel VAR.P Function

It is used to calculate the variance of the entire population.Â For example, theÂ VAR.P functionÂ can be useful in calculating variances in revenue.

## Return value

This function returns the variance of a population-based on an entire population.

## Syntax

= VAR.P(number1,[number2],…)

## Usage notes

• Number arguments 2 to 254 corresponding to a population.
• If the data is of a sample then use VAR.S Function.
• Arguments accept hard-coded value also.
• If none of the values that have been supplied to the function are numeric,#DIV/0! error-Occurs.
• If any values that are suppliedÂ directlyÂ to this function are text values that cannot be interpreted as numbers,#VALUE! error-Occurs.

### Availability

Since 2010 for Windows. Since 2011 for Mac.

The VAR.S stands for Variance for sample.In MS Excel the VAR.S function is categorized under statistical function. A variance is a statistical measure to identify the amount that the value varies from its average.This function ignores text and logical operator in the sample.

## Purpose of Excel VAR.S Function

To find the  variance based on given samples in a population.

## Return value

The VAR.S returns sample variance for a supplied set of values.

## Syntax

= VAR.S(number1,[number2],…)

## Usage notes

• If fewer than 2 numeric values have been supplied to the function #DIV/0! error will occur.
•  If any values that are supplied directly to the Var.S function are text values that cannot be interpreted as numbers, #VALUE! error will occur.
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARA function.
• Arguments can either be numbers or names, arrays, or references that contain numbers.

### Availability

Since 2010 for Windows.

The MINIFS function is a conditional minimum function. This function is categorized under statistical function in Excel. This function finds out the minimum value of the given data range.

## Purpose of Excel MINIFS Function

The MINIFS function calculates the smallest(minimum) numeric value that matches its given criteria which can be one or more.

## Return value

This function returns the minimum value in the given data.

## Syntax

= MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

## Usage notes

• The function will return #VALUE! error if the size and shape of the min_range and criteria_range1 are not the same.
• For partial matching, This functionsupports logical operators and wildcards.
• It’s a new function available only from office 365 and excel 2019.

### Availability

Since 2019 for Windows.

The Excel MAXIFS is a conditional maximum function. It is categorized under statistical functions. MAXIFS returns the maximum or largest value that meets one or more specified criteria in a range of values, it can be used based on dates, numbers, text and other conditions.

## Purpose of Exce MAXIFS Function

To get the maximum value with specified criteria.

## Return value

The MAXIFS return the largest value based on specified criteria.

## Syntax

= MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

## Usage notes

• The size and shape of the max_range and criteria_rangeN arguments must be the same, otherwise these functions return the #VALUE! error.
• The MAXIFS function can apply crieteria to dates, numbers, and text. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

### Availability

Since 2010 for Windows.
Since 2011 for Mac.

The Excel RANK.EQ Function provides the statistical rank of a given value, within a supplied array of values.The RANK.EQ Function will give same rank if there is any duplicate value in the list.

## Purpose of Excel RANK.EQ Function

To find statistical rank where a list of numbers that cannot be sorted into ascending or descending order and for a list of numbers that contain duplicates.

## Return value

RANK.EQ function returns the rank of a number in a list of numbers.

## Syntax

=RANK.EQ(number,ref,[order])

## Usage notes

• If the supplied number is not present within the supplied ref, #N/A error will occurs.
• Note that the RANK.EQ function does not recognize text representations of numbers as numeric values, so you will also get the #N/A error if the values in the supplied ref array are text values.
• Excel ranks number as if Ref were a list sorted in descending order,if the order is 0 or omitted.
• Excel ranks number as if Ref were a list sorted in ascending order,If order is any nonzero value.
• RANK.EQ gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers.

### Availability

Since 2010 for Windows.
Since 2011 for Mac.

The Excel RANK.AVG stands for rank for average.In MS Excel RANK.AVG function is categorized under the head statistical function.the statistical rank tells you the order of the value in the list.

## Purpose of Excel RANK.AVG Function

To get the rank of a number in a list of numbers.The difference between these RANK.EQ and RANK.AVG functions occurs when there are duplicates in the list of values. The Rank.EQ function returns the lower rank, whereas the RANK.AVG function returns the  average rank.

## Return value

Returns the numbers of a numeric value in the list (the sequence number relative to other values) – that is, it performs the same task. Only when identical values are found, it returns the average.

## Syntax

= RANK.AVG(number,ref,[order])

## Usage notes

• If the supplied number is not present within the supplied ref,#N/A error occurs
• The Rank.AVG function does not recognise text representations of numbers as numeric values, so you will also get the #N/A error if the values in the supplied ref array are text values).
• Excel ranks number as if ref were a list sorted in descending order,if order is 0 or omitted.
• Excel ranks number as if ref were a list sorted in ascending order,if order is any non-zero value.

### Availability

Since 2010 for Windows.
Since 2011 for Mac.

The Excel QUARTILE.INC function stands for quartile inclusive.The QUARTILE.INC function is categorized under statistical function. A quartile is a type of quantile which divides the number of data points into four more or less equal parts, or quarters.

## Purpose of Excel QUARTILE.INC Function

This function is used to get minimum and maximum value. The function can be used in revenue analysis.

## Return value

The QUARTILE.INC function returns quartile of given data on percentile value 0.1, inclusive. Its calculation is based on a percentile range of 0 to 1.

## Syntax

= QUARTILE.INC(array,quart)

## Usage notes

• It accepts five values for the quart argument, as shown in the table below:
• This functionÂ returns the #NUM! error value,if the array is empty.
• It will truncat ,if the quart is not an integer.
• If the supplied value ofÂ quartÂ is is < 0 or > 4,#NUM! error occurs.
• If the supplied value ofÂ quartÂ is non-numeric,#VALUE! error will occur.

### Availability

Since 2010 for Windows.
Since 2011 for Mac.

The Excel QUARTILE.EXC Function stands for quartile exclusive.This excel function is categorised under the staistical function. A quartile is a type of quantile which divides the number of data points into four more or less equal parts, or quarters

## Purpose of Excel QUARTILE.EXC Function

The function can be used in revenue analysis.This function does not gives minimum and maximum value as it is exclusive of 0 to 1.

## Return value

Returns the quartile of given data on percentile value 0.1, exclusive. Its calculation is based on a percentile range of 0 to 1 (exc).

## Syntax

= QUARTILE.EXC(array,quart)

## Usage notes

• This function returns the #NUM! error value,if the array is empty.
• It will be truncated, if the quart is not an integer.
• If the supplied value of quart is < 1 or > 3, #NUM! error will occur.
• If the supplied array is empty,#NUM! will occur.
• #VALUE! error occurs if the supplied value of quart is non-numeric.

### Availability

Since 2010 for Windows.
Since 2011 for Mac.

The Excel HARMEAN Function stands for harmonic mean.The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.This can only be calculated for positive values.

## Purpose of Excel HARMEAN Function

Harmonic mean can be used to calculate a mean that reduces the impact of outliers.

## Return value

Returns the calculated  harmonic mean of a supplied set of values.

## Syntax

= HARMEAN(number1, [number2], âŠ)

## Usage notes

• In current versions of Excel the function accepts  255 number arguments, but in Excel 2003 the function can only accept up to 30 number arguments.
• If any of the supplied numeric values are negative,#NUM! error will occurs.
• If any of the supplied values are non-numeric,#VALUE! error will occurs.
• Harmonic mean always< geometric mean.
• Geometric mean is always< arithmetic mean.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.

### Availability

Since 2007 for Windows.
Since 2011 for Mac.