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])

Arguments

ArgumentsDescriptionRemarks
array A range or array from which you want to return the rank. Mandatory
xThe value that you wish to find the rank for. Mandatory
[significance]Number of significant digits in result. Defaults to 3.Optional

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],…)

Arguments

ArgumentsDescriptionRemarks
number1 The first number corresponding to population.Mandatory
[number2] Number argument 2 to 254 corresponding to population.Optional

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],…)

Arguments

ArgumentsDescriptionRemarks
number1 The first number corresponding to sample.Mandatory
[number2] Number argument 2 to 254 corresponding to sample. Optional

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], …)

Arguments

ArgumentsDescriptionRemarks
min_range The minimum value of actual range. Mandatory
criteria_range1 Cell for criteria evaluation.Mandatory
criteria1 Criteria to apply on criteria_range1. Mandatory
[criteria_range2, criteria2] Additional ranges if needed for their respective criteria. Optional

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], …)

Arguments

ArgumentsDescriptionRemarks
max_range The actual range of cells in which the maximum will be determined. Mandatory
criteria_range1 Is the set of cells to evaluate with the criteria Mandatory
criteria1 Is the criteria in the form of a number, expression, or text that defines which cells will be evaluated as maximum. The same set of criteria works for the MINIIFS, SUMIFS, AND AVERAGEIFS functions. Mandatory
[criteria_range2Additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs. Optional
criteria2]Additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs. Optional

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])

Arguments

ArgumentsDescriptionRemarks
number The number to rank.Mandatory
ref An array that contains the numbers to rank against. Mandatory
[order] Ascending rank or descending order Optional

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])

Arguments

ArgumentsDescriptionRemarks
number The number to rank Mandatory
ref An array of, or a reference to, a list of numbers. Mandatory
[order] Specifying ascending or descending order. Optional

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)

Arguments

ArgumentsDescriptionRemarks
array List of value to calculate quartile.Mandatory
quart Indicate which value to return.
Mandatory

Usage notes

  • It accepts five values for the quart argument, as shown in the table below:
IF QUARTILE EQUAL QUARTILE.INC RETURNS
0Minimum value
1 First quartile point – 25th percentile
2 Median value – 50th percentile
3 Third quartile – 75th percentile
4Maximum value
  • 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)

Arguments

ArgumentsDescriptionRemarks
array List of value to calculate quartile.Mandatory
quart Indicate which value to return.
Mandatory

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.
IF QUARTILE EQUAL QUARTILE.EXC RETURNS
1 First quartile point – 25th percentile
2 Median value – 50th percentile
3 Third quartile – 75th percentile

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], …)

Arguments

ArgumentsDescriptionRemarks
number1 First number.Mandatory
[number2] Second number
Optional

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.