XLS Statistics Functions Excel Add-In
This Excel addin adds the following new functions to excel:
- DOWNSIDEDEVIATION
- GAINLOSSRATIO
- GAINSTANDARDDEVIATION
- LOSSSTANDARDDEVIATION
- SEMIDEVIATION
- COMPOUNDRETURN
- GAINMEAN
- LOSSMEAN
- COVARIANCE
- FIVENUMBERSUMMARYINPLACE
- GEOMETRICMEAN
- HARMONICMEAN
- INTERQUARTILERANGEINPLACE
- LOWERQUARTILEINPLACE
- MAXIMUM
- MAXIMUMABSOLUTE
- MEAN
- MEANSTANDARDDEVIATION
- MEANVARIANCE
- MEDIANINPLACE
- MINIMUM
- MINIMUMABSOLUTE
- ORDERSTATISTICINPLACE
- PERCENTILEINPLACE
- POPULATIONCOVARIANCE
- POPULATIONSTANDARDDEVIATION
- POPULATIONVARIANCE
- QUANTILECUSTOMINPLACE
- QUANTILEINPLACE
- RANKSINPLACE
- ROOTMEANSQUARE
- STANDARDDEVIATION
- UPPERQUARTILEINPLACE
- VARIANCE
Below you will find a description of all additional functions:
DOWNSIDEDEVIATION
This measure is similar to the loss standard deviation except the downside deviation considers only returns that fall below a defined minimum acceptable return (MAR) rather than the arithmetic mean. For example, if the MAR is 7%, the downside deviation would measure the variation of each period that falls below 7%. (The loss standard deviation, on the other hand, would take only losing periods, calculate an average return for the losing periods, and then measure the variation between each losing return and the losing return average).
GAINLOSSRATIO
Measures a fund’s average gain in a gain period divided by the fund’s average loss in a losing period. Periods can be monthly or quarterly depending on the data frequency.
GAINSTANDARDDEVIATION
Calculation is similar to Standard Deviation , except it calculates an average (mean) return only for periods with a gain and measures the variation of only the gain periods around the gain mean. Measures the volatility of upside performance. © Copyright 1996, 1999 Gary L.Gastineau. First Edition. © 1992 Swiss Bank Corporation.
LOSSSTANDARDDEVIATION
Similar to standard deviation, except this statistic calculates an average (mean) return for only the periods with a loss and then measures the variation of only the losing periods around this loss mean. This statistic measures the volatility of downside performance.
SEMIDEVIATION
A measure of volatility in returns below the mean. It's similar to standard deviation, but it only looks at periods where the investment return was less than average return.
COMPOUNDRETURN
Compound Monthly Return or Geometric Return or Annualized Return
GAINMEAN
Average Gain or Gain Mean This is a simple average (arithmetic mean) of the periods with a gain. It is calculated by summing the returns for gain periods (return 0) and then dividing the total by the number of gain periods.
LOSSMEAN
Average Loss or LossMean This is a simple average (arithmetic mean) of the periods with a loss. It is calculated by summing the returns for loss periods (return < 0) and then dividing the total by the number of loss periods.
COVARIANCE
Estimates the unbiased population covariance from the provided two sample arrays. On a dataset of size N will use an N-1 normalizer (Bessel's correction). Returns NaN if data has less than two entries or if any entry is NaN.
FIVENUMBERSUMMARYINPLACE
Estimates {min, lower-quantile, median, upper-quantile, max} from the unsorted data array. Approximately median-unbiased regardless of the sample distribution (R8). WARNING: Works inplace and can thus causes the data array to be reordered.
GEOMETRICMEAN
Evaluates the geometric mean of the unsorted data array. Returns NaN if data is empty or any entry is NaN.
HARMONICMEAN
Evaluates the harmonic mean of the unsorted data array. Returns NaN if data is empty or any entry is NaN.
INTERQUARTILERANGEINPLACE
Estimates the inter-quartile range from the unsorted data array. Approximately median-unbiased regardless of the sample distribution (R8). WARNING: Works inplace and can thus causes the data array to be reordered.
LOWERQUARTILEINPLACE
Estimates the first quartile value from the unsorted data array. Approximately median-unbiased regardless of the sample distribution (R8). WARNING: Works inplace and can thus causes the data array to be reordered.
MAXIMUM
Returns the largest value from the unsorted data array. Returns NaN if data is empty or any entry is NaN.
MAXIMUMABSOLUTE
Returns the largest absolute value from the unsorted data array. Returns NaN if data is empty or any entry is NaN.
MEAN
Estimates the arithmetic sample mean from the unsorted data array. Returns NaN if data is empty or any entry is NaN.
MEANSTANDARDDEVIATION
Estimates the arithmetic sample mean and the unbiased population standard deviation from the provided samples as unsorted array. On a dataset of size N will use an N-1 normalizer (Bessel's correction). Returns NaN for mean if data is empty or any entry is NaN and NaN for standard deviation if data has less than two entries or if any entry is NaN.
MEANVARIANCE
Estimates the arithmetic sample mean and the unbiased population variance from the provided samples as unsorted array. On a dataset of size N will use an N-1 normalizer (Bessel's correction). Returns NaN for mean if data is empty or any entry is NaN and NaN for variance if data has less than two entries or if any entry is NaN.
MEDIANINPLACE
Estimates the median value from the unsorted data array. WARNING: Works inplace and can thus causes the data array to be reordered.
MINIMUM
Returns the smallest value from the unsorted data array. Returns NaN if data is empty or any entry is NaN.
MINIMUMABSOLUTE
Returns the smallest absolute value from the unsorted data array. Returns NaN if data is empty or any entry is NaN.
ORDERSTATISTICINPLACE
Returns the order statistic (order 1..N) from the unsorted data array. WARNING: Works inplace and can thus causes the data array to be reordered.
PERCENTILEINPLACE
Estimates the p-Percentile value from the unsorted data array. If a non-integer Percentile is needed, use Quantile instead. Approximately median-unbiased regardless of the sample distribution (R8). WARNING: Works inplace and can thus causes the data array to be reordered.
POPULATIONCOVARIANCE
Evaluates the population covariance from the full population provided as two arrays. On a dataset of size N will use an N normalizer and would thus be biased if applied to a subset. Returns NaN if data is empty or if any entry is NaN.
POPULATIONSTANDARDDEVIATION
Evaluates the population standard deviation from the full population provided as unsorted array. On a dataset of size N will use an N normalizer and would thus be biased if applied to a subset. Returns NaN if data is empty or if any entry is NaN.
POPULATIONVARIANCE
Evaluates the population variance from the full population provided as unsorted array. On a dataset of size N will use an N normalizer and would thus be biased if applied to a subset. Returns NaN if data is empty or if any entry is NaN.
QUANTILECUSTOMINPLACE
Estimates the tau-th quantile from the unsorted data array. The tau-th quantile is the data value where the cumulative distribution function crosses tau. The quantile definition can be specified by 4 parameters a, b, c and d, consistent with Mathematica. WARNING: Works inplace and can thus causes the data array to be reordered.
QUANTILEINPLACE
Estimates the tau-th quantile from the unsorted data array. The tau-th quantile is the data value where the cumulative distribution function crosses tau. Approximately median-unbiased regardless of the sample distribution (R8). WARNING: Works inplace and can thus causes the data array to be reordered.
RANKSINPLACE
Evaluates the rank of each entry of the unsorted data array. The rank definition can be specified to be compatible with an existing system. WARNING: Works inplace and can thus causes the data array to be reordered.
ROOTMEANSQUARE
Estimates the root mean square (RMS) also known as quadratic mean from the unsorted data array. Returns NaN if data is empty or any entry is NaN.
STANDARDDEVIATION
Estimates the unbiased population standard deviation from the provided samples as unsorted array. On a dataset of size N will use an N-1 normalizer (Bessel's correction). Returns NaN if data has less than two entries or if any entry is NaN.
UPPERQUARTILEINPLACE
Estimates the third quartile value from the unsorted data array. Approximately median-unbiased regardless of the sample distribution (R8). WARNING: Works inplace and can thus causes the data array to be reordered.
VARIANCE
Estimates the unbiased population variance from the provided samples as unsorted array. On a dataset of size N will use an N-1 normalizer (Bessel's correction). Returns NaN if data has less than two entries or if any entry is NaN.