Wednesday, 22 March 2017

Excel - Microsoft Excel Percentile.Exc Function

Description

The Excel PERCENTILE.EXC function returns the k'th percentile of a supplied range of values for a given value of k, within the range 0 to 1 (exclusive). 

The function is new in Excel 2010 and so is not available in earlier versions of Excel. 

The format of the Percentile.Exc function is : 

PERCENTILE.EXC( array, k ) 



Where the function arguments are: 


If k is not a multiple of 1/(n+1), (where n is the number of values in the supplied array), the function interpolates between the values in the supplied array, to calculate the percentile value. However, if k is < 1/(n+1) or k is > n/(n+1), the function is unable to interpolate, and so returns an error. 

Percentile.Exc Function Examples

The spreadsheets below show examples of the Excel Percentile.Exc function used to calculate the k'th percentile of a set of values in cells A1 - A4 of the sample spreadsheet, for different values of k. 

The formulas for the functions are shown in the spreadsheet on the left, and the results are shown in the spreadsheet on the right. 



Percentile.Exc Function Errors

If you get an error from the Excel Percentile.Exc function this is likely to be one of the following: 
Common Errors 


Excel - Microsoft Excel MODEMULTI Function

Description

The Excel MODE.MULT function returns a vertical array of the statistical modes (the most frequently occurring values) within a list of supplied numbers. 

The function is new in Excel 2010 and so is not available in earlier versions of Excel. 

The format of the Mode.Mult function is : 

MODE.MULT( number1, [number2], ... ) 

where the number arguments are up to 254 numeric values, or arrays of numeric values, for which you want to calculate the mode. 

As the Mode.Mult function returns an array of values, it must be entered as an array formula. If the function is not entered as an array formula, only the first mode is returned, which is the same as using the MODE.SNGL function. 

Array Formulas: 


To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details. 


Mode.Mult Function Example

Cells B1 - B2 of the spreadsheet on the right shows the Mode.Mult function, used to return the modes of the values in cells A1 - A10. 
Clearly the data set has two modes, the values 3 and 5, and so the Mode.Mult function will return a vertical array containing these two values. It must therefore be entered as an array formula, into the two vertical cells B1 - B2. 
The curly brackets seen around the function in the formula bar, show that it function has been entered as an array formula. 

Mode.Mult Function Errors

If you get an error from the Excel Mode.Mult function this is likely to be one of the following: 
Common Errors 

Excel - Microsoft Excel MODE.SNGL Function

Description

The Excel MODE.SNGL function returns the statistical mode (the most frequently occurring value) of a list of supplied numbers. If there are 2 or more most frequently occurring values in the supplied data, the function returns the lowest of these values 

The Mode.Sngl function is new in Excel 2010 and so is not available in earlier versions of Excel. However, the function is simply a renamed version of the Mode function that is available in earlier versions of Excel. 

The format of the Mode.Sngl function is : 

MODE.SNGL( number1, [number2], ... ) 


Where the number arguments are up to 255 numeric values, or arrays of numeric values, for which you want to calculate the mode. 

Mode.Sngl Function Examples

The following example shows the Mode.Sngl function, used to calculate the mode of the values in cells A1 - A10. 

There are two statistical modes, 3 and 4, and so the Mode.Sngl function returns the lowest of these two values. 

Microsoft Excel MODE.SNGL Function

Mode.Sngl Function Errors
If you get an error from the Excel Mode.Sngl function this is likely to be one of the following: 
Common Errors 


Excel - Microsoft Excel MODE Function

Description

The Excel MODE function returns the statistical mode (the most frequently occurring value) of a list of supplied numbers. If there are 2 or more most frequently occurring values in the supplied data, the function returns the lowest of these values 

The format of the function is : 

MODE( number1, [number2], ... ) 


where the number arguments are a set of one or more numeric values, or arrays of numeric values, for which you want to calculate the mode. In Excel 2007 and Excel 2010, you can supply up to 255 number arguments to the Mode function, but in Excel 2003, the function can only accept up to 30 number arguments. 

Text and logical values within a supplied array are ignored by the function. 

Mode Function Examples

The following example shows the Mode function, used to calculate the mode of the set of values in cells A1 - A10. 


The data has two statistical modes, 3 and 4, and so the Mode.Sngl function returns the lowest of these two values. 


Mode Function Errors

If you get an error from the Excel Mode function this is likely to be one of the following: 
Common Errors 




Excel - Microsoft Excel MINA Function

Description

The Excel MINA function returns the smallest value from a supplied set of numerical values, while counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1. 

The syntax of the function is : 

MINA( number1, [number2], ... ) 

where the number arguments are a set of one or more numeric values, or arrays of numeric values, that you want to return the smallest value of. These values can be supplied either directly to the function, as cells containing numeric values, or as values returned from other excel formulas. 

In Excel 2007 & Excel 2010, you can provide up to 255 number arguments to the Mina function, but in Excel 2003 or earlier, you can only provide up to 30 number arguments. 

Excel Mina Function Example

The following spreadsheet shows examples of use of the Mina function. 

The function formats are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right. 

Mina Function Error

If you get an error from the Excel Mina Function, this is likely to be the #VALUE! error: 
Common Error 

Excel - Microsoft Excel MIN Function

Description

The Excel MIN function returns the smallest value from a supplied set of numerical values. 

The syntax of the function is : 

MIN( number1, [number2], ... ) 

Where the number arguments are a set of one or more numeric values, or arrays of numeric values, that you want to return the smallest value of. These values can be supplied directly to the function, as cells containing numeric values, or as values returned from other excel formulas. 

In Excel 2007 & Excel 2010, you can provide up to 255 number arguments to the function, but in Excel 2003 or earlier, you can only provide up to 30 number arguments. 

If an argument is supplied to the function as a reference to a cell, or an array of cells, the Min function will ignore blank cells, and text or logical values contained within the supplied cell range. However, logical values and text representations of numbers that are supplied directly to the function will be included in the calculation. 

Excel Min Function Example

The following example shows the Min function used to retrieve the smallest value from the set of values in cells A1 - A5. 

The format of the function is shown in the spreadsheet on the left and the resulting value is shown in the spreadsheet on the right. 



Min Function Error

If you get an error from the Excel Min Function, this is likely to be the #VALUE! error: 
Common Error 

Excel - Microsoft Excel MEDIAN Function

Description

The Excel MEDIAN function returns the statistical median (the middle value) of a list of supplied numbers. 

The format of the function is: 

MEDIAN( number1, [number2], ... ) 


where the number arguments are a set of one or more numeric values, or arrays of numeric values, for which you want to calculate the median - these values can be supplied directly to the function, or as one or more cells or ranges of cells containing numeric values. 

Also note that in Excel 2007 & Excel 2010, you can provide up to 255 number arguments to the function, but in Excel 2003, you can only provide up to 30 number arguments. 

Excel Median Function Examples

The following example shows the function used to calculate the median of the set of values in cells A1 - A5. Although the same 5 values are provided to each of the functions in cells B1 - B3, in each of these 3 examples, the values are provided to the function in different ways. 

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right. 

In the example below, there is an even number of values supplied to the median function, so the median is calculated to be the average of the 2 middle values. 



Note that in the example above, the blank cell A5 is included in the range that is supplied to the median function. However, because this cell is blank, it is ignored in the calculation.