Statistical Formulas

-

 

  1. Mean (Average):

    • Syntax: =AVERAGE(range)
    • Example: =AVERAGE(A1:A10)
    • Calculates the mean (average) of a range of values.


  3. Median:

    • Syntax: =MEDIAN(range)
    • Example: =MEDIAN(A1:A10)
    • Finds the median (middle value) of a range of values.

  4. Mode:

    • Syntax: =MODE.SNGL(range)
    • Example: =MODE.SNGL(A1:A10)
    • Identifies the most frequently occurring value in a range.

  5. Standard Deviation:

    • Syntax: =STDEV.P(range) or =STDEV.S(range)
    • Example: =STDEV.P(A1:A10) or =STDEV.S(A1:A10)
    • Calculates the standard deviation for a population (STDEV.P) or a sample (STDEV.S).

  6. Variance:

    • Syntax: =VAR.P(range) or =VAR.S(range)
    • Example: =VAR.P(A1:A10) or =VAR.S(A1:A10)
    • Computes the variance for a population (VAR.P) or a sample (VAR.S).

  7. Correlation Coefficient:

    • Syntax: =CORREL(range1, range2)
    • Example: =CORREL(A1:A10, B1:B10)
    • Calculates the correlation coefficient between two sets of values.

  8. Regression Analysis (Slope):

    • Syntax: =SLOPE(known_y's, known_x's)
    • Example: =SLOPE(B1:B10, A1:A10)
    • Computes the slope of the linear regression line.

  9. Regression Analysis (Intercept):

    • Syntax: =INTERCEPT(known_y's, known_x's)
    • Example: =INTERCEPT(B1:B10, A1:A10)
    • Calculates the y-intercept of the linear regression line.

  10. Percentile:

    • Syntax: =PERCENTILE.INC(range, k)
    • Example: =PERCENTILE.INC(A1:A10, 0.75)
    • Finds the k-th percentile of a dataset.

  11. Z-Score:

    • Syntax: =Z.TEST(range, [sigma])
    • Example: =Z.TEST(A1:A10)
    • Calculates the Z-score for a dataset, assuming a normal distribution.

  12. Quartiles:

    • Syntax (First Quartile Q1): =QUARTILE.INC(range, 1)
    • Syntax (Second Quartile Q2/Median): =QUARTILE.INC(range, 2)
    • Syntax (Third Quartile Q3): =QUARTILE.INC(range, 3)
    • Example: =QUARTILE.INC(A1:A10, 1), =QUARTILE.INC(A1:A10, 2), =QUARTILE.INC(A1:A10, 3)
    • Computes the quartiles of a dataset.

  13. Range:

    • Syntax: =MAX(range) - MIN(range)
    • Example: =MAX(A1:A10) - MIN(A1:A10)
    • Calculates the range, which is the difference between the maximum and minimum values in a dataset.

  14. Geometric Mean:

    • Syntax: =GEOMEAN(range)
    • Example: =GEOMEAN(A1:A10)
    • Computes the geometric mean of a dataset.

  15. Harmonic Mean:

    • Syntax: =HARMEAN(range)
    • Example: =HARMEAN(A1:A10)
    • Calculates the harmonic mean of a dataset.

  16. Skewness:

    • Syntax: =SKEW.P(range) or =SKEW.S(range)
    • Example: =SKEW.P(A1:A10) or =SKEW.S(A1:A10)
    • Measures the skewness of a distribution for a population (SKEW.P) or a sample (SKEW.S).

  17. Kurtosis:

    • Syntax: =KURT.P(range) or =KURT.S(range)
    • Example: =KURT.P(A1:A10) or =KURT.S(A1:A10)
    • Measures the kurtosis (tailedness) of a distribution for a population (KURT.P) or a sample (KURT.S).

  18. Chi-Square Test:

    • Syntax: =CHISQ.TEST(observed_range, expected_range)
    • Example: =CHISQ.TEST(A1:A10, B1:B10)
    • Conducts a chi-square test for independence.

  19. Covariance:
    • Syntax: =COVARIANCE.P(range1, range2) or =COVARIANCE.S(range1, range2)
    • Example: =COVARIANCE.P(A1:A10, B1:B10) or =COVARIANCE.S(A1:A10, B1:B10)
    • Computes the covariance between two sets of values for a population (COVARIANCE.P) or a sample (COVARIANCE.S).
    1. Exponential Moving Average (EMA):
    • Syntax: =EMA(range, smoothing_factor)
    • Example: =EMA(A1:A10, 0.2)
    • Calculates the exponential moving average with a specified smoothing factor.
    1. Pearson Correlation Coefficient:
    • Syntax: =PEARSON(range1, range2)
    • Example: =PEARSON(A1:A10, B1:B10)
    • Measures the strength and direction of the linear relationship between two sets of values.
    1. T-Test:
    • Syntax: =T.TEST(array1, array2, tails, type)
    • Example: =T.TEST(A1:A10, B1:B10, 2, 1)
    • Conducts a t-test to determine if there is a significant difference between two sets of values.
    1. F-Test:
    • Syntax: =F.TEST(array1, array2)
    • Example: =F.TEST(A1:A10, B1:B10)
    • Performs an F-test to compare the variances of two sets of values.
    1. Covariance Matrix:
    • Syntax: =COVARIANCE.SNGL(matrix)
    • Example: =COVARIANCE.SNGL(A1:C10)
    • Computes the covariance matrix for a range of values.
    1. Descriptive Statistics:
    • Syntax: =DESCRIPTIVE.STAT(range)
    • Example: =DESCRIPTIVE.STAT(A1:A10)
    • Provides a summary of descriptive statistics for a range of values, including mean, standard deviation, minimum, maximum, etc.

  20. Frequency Distribution:

    • Syntax: =FREQUENCY(data_array, bins_array)
    • Example: =FREQUENCY(A1:A10, {0, 10, 20, 30})
    • Creates a frequency distribution based on specified bins.

  21. Confidence Interval for Mean:

    • Syntax: =CONFIDENCE.T(alpha, stdev, size)
    • Example: =CONFIDENCE.T(0.05, STDEV(A1:A10), COUNT(A1:A10))
    • Calculates the confidence interval for the mean using t-distribution.

  22. Two-Sample T-Test:

    • Syntax: =T.TEST(range1, range2, tails, type)
    • Example: =T.TEST(A1:A10, B1:B10, 2, 3)
    • Conducts a two-sample t-test for the equality of means.

  23. ANOVA (Analysis of Variance):

    • Syntax: =ANOVA(range1, range2, ...)
    • Example: =ANOVA(A1:A10, B1:B10, C1:C10)
    • Performs a one-way analysis of variance.

  24. Exponential Smoothing:

    • Syntax: =FORECAST.ETS(range, timeline, [seasonality], [alpha], [beta], [gamma], [phi], [M], [opt])
    • Example: =FORECAST.ETS(A1:A10, B1:B10, 1, 0.3, 0.1, 0.2, 0.5, 12, TRUE)
    • Applies exponential smoothing to time series data.

  25. Percentile Rank:

    • Syntax: =PERCENTRANK.INC(range, x, [significance])
    • Example: =PERCENTRANK.INC(A1:A10, A1)
    • Calculates the percentile rank of a value within a dataset.

  26. Covariance:

    • Syntax: =COVARIANCE.P(range1, range2) or =COVARIANCE.S(range1, range2)
    • Example: =COVARIANCE.P(A1:A10, B1:B10) or =COVARIANCE.S(A1:A10, B1:B10)
    • Computes the covariance between two sets of values for a population (COVARIANCE.P) or a sample (COVARIANCE.S).

  27. Covariance Matrix:

    • Syntax: =COVARIANCE.P(matrix1, matrix2) or =COVARIANCE.S(matrix1, matrix2)
    • Example: =COVARIANCE.P(C1:E10, F1:H10) or =COVARIANCE.S(C1:E10, F1:H10)
    • Computes the covariance matrix between two sets of matrices for a population (COVARIANCE.P) or a sample (COVARIANCE.S).

  28. Pearson Correlation Coefficient:

    • Syntax: =CORREL(array1, array2)
    • Example: =CORREL(A1:A10, B1:B10)
    • Calculates the Pearson correlation coefficient between two sets of values.

  29. Critical Value for T-Distribution:

    • Syntax: =T.INV.2T(probability, degrees_freedom)
    • Example: =T.INV.2T(0.05, 8)
    • Determines the critical value for a two-tailed T-distribution.

  30. Chi-Square Test for Independence:

    • Syntax: =CHISQ.TEST(observed_range, expected_range)
    • Example: =CHISQ.TEST(A1:A10, B1:B10)
    • Conducts a chi-square test for independence between two categorical variables.

  31. Fisher Transformation for Correlation:

    • Syntax: =ATANH(correlation_coefficient)
    • Example: =ATANH(CORREL(A1:A10, B1:B10))
    • Applies the Fisher transformation to the Pearson correlation coefficient.

  32. Moving Average:

    • Syntax: =AVERAGE(range)
    • Example: =AVERAGE(OFFSET(A1,0,0,5,1))
    • Computes the moving average of a dataset by using the AVERAGE function with OFFSET.

  33. One-Sample Z-Test:
    • Syntax: =Z.TEST(range, [mu])
    • Example: =Z.TEST(A1:A10, 70)
    • Conducts a one-sample z-test for the population mean.
    1. Confidence Interval for Proportion:
    • Syntax: =CONFIDENCE.NORM(alpha, stdev, size)
    • Example: =CONFIDENCE.NORM(0.05, 0.02, 100)
    • Calculates the confidence interval for a population proportion using the normal distribution.
    1. Exponential Distribution Probability:
    • Syntax: =EXP.DIST(x, lambda, [cumulative])
    • Example: =EXP.DIST(A1, 0.1, TRUE)
    • Computes the probability of an exponential distribution.
    1. Hypothesis Test for Population Variance:
    • Syntax: =VAR.P(range)*((COUNT(range)-1)/CHISQ.INV(alpha/2, COUNT(range)-1)) <= VAR.S(range) <= VAR.P(range)*((COUNT(range)-1)/CHISQ.INV(1-alpha/2, COUNT(range)-1))
    • Example: Conducts a hypothesis test for the population variance.
    1. F-Test for Two-Sample Variances:
    • Syntax: =F.TEST(array1, array2)
    • Example: =F.TEST(A1:A10, B1:B10)
    • Conducts an F-test to compare the variances of two samples.
  34. Moving Standard Deviation:
    • Syntax: =STDEV(range)
    • Example: =STDEV(OFFSET(A1,0,0,5,1))
    • Calculates the moving standard deviation of a dataset by using the STDEV function with OFFSET.
    1. Kolmogorov-Smirnov Test:
    • Syntax: =KSTEST(range, distribution, [location], [scale])
    • Example: =KSTEST(A1:A10, "NORM", MEAN(A1:A10), STDEV(A1:A10))
    • Performs the Kolmogorov-Smirnov test for goodness of fit.
    1. Anderson-Darling Test:
    • Syntax: =A.TEST(range, [distribution])
    • Example: =A.TEST(A1:A10, "NORM")
    • Conducts the Anderson-Darling test for normality.
    1. Wilcoxon Signed-Rank Test:
    • Syntax: =WILCOXON(range1, [range2], [output_range])
    • Example: =WILCOXON(A1:A10, B1:B10)
    • Performs the Wilcoxon signed-rank test for paired samples.
    1. Mann-Whitney U Test:
    • Syntax: =MANNWHITNEY.U(array1, array2, [output_range])
    • Example: =MANNWHITNEY.U(A1:A10, B1:B10)
    • Conducts the Mann-Whitney U test for independent samples.
  1. Durbin-Watson Statistic:
  • Syntax: =DURBIN.WATSON(range)
  • Example: =DURBIN.WATSON(A1:A10)
  • Calculates the Durbin-Watson statistic to test for autocorrelation in residuals.
  1. Covariance Matrix for Multivariate Data:
  • Syntax: =COVARIANCE.M(array1, array2)
  • Example: =COVARIANCE.M(C1:C10, D1:D10)
  • Computes the covariance between two sets of variables in multivariate data.
  1. Linear Regression Coefficients:
  • Syntax (Slope): =INDEX(LINEST(y_values, x_values),1)
  • Syntax (Intercept): =INDEX(LINEST(y_values, x_values),1,2)
  • Example: Slope =INDEX(LINEST(B1:B10, A1:A10),1), Intercept =INDEX(LINEST(B1:B10, A1:A10),1,2)
  • Retrieves the coefficients of a linear regression model using the LINEST function.
  1. R-Squared (Coefficient of Determination):
  • Syntax: =RSQ(known_y's, known_x's)
  • Example: =RSQ(B1:B10, A1:A10)
  • Calculates the R-squared value for a linear regression model.
  1. Logarithmic Regression:
  • Syntax (Slope): =INDEX(LOGEST(y_values, x_values),1)
  • Syntax (Intercept): =INDEX(LOGEST(y_values, x_values),1,2)
  • Example: Slope =INDEX(LOGEST(B1:B10, A1:A10),1), Intercept =INDEX(LOGEST(B1:B10, A1:A10),1,2)
  • Retrieves the coefficients for a logarithmic regression model using the LOGEST function.
  1. Harmonic Mean for More Than Two Values:
  • Syntax: =HARMEAN(range1, range2, ...)
  • Example: =HARMEAN(A1:A5, B1:B5, C1:C5)
  • Calculates the harmonic mean for more than two sets of values.
  1. Percentile Exclusive:
  • Syntax: =PERCENTILE.EXC(range, k)
  • Example: =PERCENTILE.EXC(A1:A10, 0.75)
  • Computes the exclusive k-th percentile of a dataset.
  1. Anderson-Darling Test for Normal Distribution:
  • Syntax: =AGGREGATE(14,6,1/(STDEVP(range)*(COUNT(range)/4)^0.2)*(1.128+1.332/(COUNT(range)^0.5)*(SORTBY(range,ROW(range))-AVERAGE(range))),1)
  • Example: Performs the Anderson-Darling test for normality using the AGGREGATE and SORTBY functions.
  1. VIF (Variance Inflation Factor) for Multicollinearity:
  • Syntax: =1/(1-R^2)
  • Example: =1/(1-CORREL(A1:A10, B1:B10)^2)
  • Calculates the VIF to detect multicollinearity in regression analysis.
  1. Spearman's Rank Correlation Coefficient:
  • Syntax: =CORREL(RANK.EQ(A1:A10), RANK.EQ(B1:B10))
  • Example: Calculates Spearman's rank correlation coefficient using the RANK.EQ function.
  1. Minkowski Distance:
  • Syntax: =POWER(SUMXMY2(range1, range2),1/p)
  • Example: =POWER(SUMXMY2(A1:A10, B1:B10), 1/3)
  • Calculates the Minkowski distance between two vectors.
  1. Bayesian Information Criterion (BIC):
  • Syntax: =LN(COUNT(range))*(number_of_parameters) - 2*LN(LSQ(known_y's, LINEST(known_y's, known_x's))))
  • Example: Constructs the BIC for model selection using the LINEST and LSQ functions.
  1. Geometric Standard Deviation:
  • Syntax: =EXP(STDEV.LN(range))
  • Example: =EXP(STDEV.LN(A1:A10))
  • Calculates the geometric standard deviation of a dataset.
  1. Cochran's Q Test:
  • Syntax: =COUNT(range)*(SUM(range)-COUNT(range)*(COUNT(range)+1)/(12*(COUNT(range)+1)))
  • Example: =COUNT(A1:A10)*(SUM(A1:A10)-COUNT(A1:A10)*(COUNT(A1:A10)+1)/(12*(COUNT(A1:A10)+1)))
  • Performs Cochran's Q test for dependent proportions.
  1. Robust Standard Deviation:
  • Syntax: =IF(MEDIAN(IF(ISNUMBER(range),range))<>0,IQR(range)/(2*MEDIAN(IF(ISNUMBER(range),range))*NORM.S.INV(1.5*0.75)))
  • Example: Calculates the robust standard deviation using the IF, MEDIAN, ISNUMBER, IQR, and NORM.S.INV functions.
  1. Cochran-Armitage Trend Test:
  • Syntax: =CHITEST(matrix, trend_matrix)
  • Example: =CHITEST(A1:B10, C1:C10)
  • Performs the Cochran-Armitage trend test for categorical data.
  1. Critical Value for Chi-Square Distribution:
  • Syntax: =CHISQ.INV.RT(probability, degrees_freedom)
  • Example: =CHISQ.INV.RT(0.05, 8)
  • Determines the critical value for the right-tailed chi-square distribution.
  1. Exponential Moving Average (EMA):
  • Syntax: =EMA(range, smoothing_factor)
  • Example: =EMA(A1:A10, 0.2)
  • Calculates the exponential moving average of a dataset.
  1. Yield to Maturity (YTM) for Bonds:
  • Syntax: =YIELD(settlement, maturity, rate, price, redemption, frequency, [basis])
  • Example: =YIELD("1/1/2022", "12/31/2025", 0.05, 950, 1000, 2, 1)
  • Calculates the yield to maturity for a bond.
  1. Jarque-Bera Test for Normality:
  • Syntax: =COUNT(range)*(SKEW(range)^2/6 + KURT(range)^2/24)
  • Example: =COUNT(A1:A10)*(SKEW(A1:A10)^2/6 + KURT(A1:A10)^2/24)
  • Performs the Jarque-Bera test for normality.
  1. Brown-Forsythe Test:
  • Syntax: =BROWN.FORSYTHE(array1, array2, ...)
  • Example: =BROWN.FORSYTHE(A1:A10, B1:B10, C1:C10)
  • Conducts the Brown-Forsythe test for homogeneity of variances.
  1. Q-Q Plot:
  • Syntax: Requires plotting the quantiles of a sample against the quantiles of a theoretical distribution.
  • Example: Create a scatter plot with the sample quantiles on one axis and theoretical quantiles on the other.
  1. Winsorized Mean:
  • Syntax: =TRIMMEAN(range, proportion)
  • Example: =TRIMMEAN(A1:A10, 0.1)
  • Calculates the winsorized mean by trimming a specified proportion of extreme values.
  1. Probit Regression Coefficients:
  • Syntax (Slope): =INDEX(LINEST(Y_range, X_range),1)
  • Syntax (Intercept): =INDEX(LINEST(Y_range, X_range),1,2)
  • Example: Slope =INDEX(LINEST(B1:B10, A1:A10),1), Intercept =INDEX(LINEST(B1:B10, A1:A10),1,2)
  • Retrieves the coefficients of a probit regression model using the LINEST function.
  1. Spatial Autocorrelation (Moran's I):
  • Syntax: =MORANS.I(data_range, spatial_weights)
  • Example: Requires setting up spatial weights and applying the Moran's I test using the MORANS.I function.
  1. Hypergeometric Distribution Probability:
  • Syntax: =HYPGEOM.DIST(x, N, n, K, [cumulative])
  • Example: =HYPGEOM.DIST(2, 10, 4, 5, FALSE)
  • Calculates the probability of a hypergeometric distribution.
  1. Bernoulli Distribution Probability:
  • Syntax: =BERNOULLI.DIST(x, probability)
  • Example: =BERNOULLI.DIST(1, 0.3)
  • Computes the probability of a Bernoulli distribution.
  1. Negative Binomial Distribution Probability:
  • Syntax: =NEGBINOM.DIST(x, r, p, [cumulative])
  • Example: =NEGBINOM.DIST(3, 5, 0.2, FALSE)
  • Calculates the probability of a negative binomial distribution.
  1. Poisson Distribution Probability:
  • Syntax: =POISSON.DIST(x, mean, [cumulative])
  • Example: =POISSON.DIST(2, 3, FALSE)
  • Computes the probability of a Poisson distribution.
  1. Weibull Distribution Probability:
  • Syntax: =WEIBULL.DIST(x, alpha, beta, [cumulative])
  • Example: =WEIBULL.DIST(5, 2, 3, FALSE)
  • Calculates the probability of a Weibull distribution.
  1. Logit Regression Coefficients:
  • Syntax (Slope): =INDEX(LINEST(Y_range, X_range),1)
  • Syntax (Intercept): =INDEX(LINEST(Y_range, X_range),1,2)
  • Example: Slope =INDEX(LINEST(B1:B10, A1:A10),1), Intercept =INDEX(LINEST(B1:B10, A1:A10),1,2)
  • Retrieves the coefficients of a logistic regression model using the LINEST function.
  1. Kurtosis Excess:
  • Syntax: =(KURT(range)-3)
  • Example: =(KURT(A1:A10)-3)
  • Calculates the excess kurtosis of a dataset.
  1. Durbin's h Statistic for Autocorrelation:
  • Syntax: =SUMXMY2(range, OFFSET(range,1,0))/(SUMXMY2(range, range))
  • Example: =SUMXMY2(A1:A10, OFFSET(A1:A10,1,0))/(SUMXMY2(A1:A10, A1:A10))
  • Computes Durbin's h statistic for testing autocorrelation.
  1. Pareto Distribution Probability:
  • Syntax: =PARETO.DIST(x, alpha, beta, [cumulative])
  • Example: =PARETO.DIST(10, 2, 5, FALSE)
  • Calculates the probability of a Pareto distribution.
  1. Cramer's V for Association in Contingency Tables:
  • Syntax: =SQRT(CHISQ.TEST(observed_range, expected_range)/(COUNT(observed_range)*(MIN(COLUMNS(observed_range),ROWS(observed_range))-1))
  • Example: Requires setting up contingency tables and applying Cramer's V formula.
  1. Percent Change:
  • Syntax: =((New Value - Old Value) / Old Value) * 100
  • Example: =((B2 - A2) / A2) * 100
  • Calculates the percentage change between two values.
  1. Interquartile Range (IQR):
  • Syntax: =QUARTILE.INC(range,3) - QUARTILE.INC(range,1)
  • Example: =QUARTILE.INC(A1:A10,3) - QUARTILE.INC(A1:A10,1)
  • Measures the range between the first quartile (Q1) and third quartile (Q3) in a dataset.
  1. Coefficient of Variation (CV):
  • Syntax: =(STDEV.P(range) / AVERAGE(range)) * 100
  • Example: =(STDEV.P(A1:A10) / AVERAGE(A1:A10)) * 100
  • Calculates the coefficient of variation, a measure of relative variability.
  1. Chi-Square Goodness-of-Fit Test:
  • Syntax: =CHISQ.TEST(observed_range, expected_range)
  • Example: =CHISQ.TEST(A1:A10, B1:B10)
  • Conducts a chi-square test for goodness-of-fit.
  1. Log-likelihood for Log-Normal Distribution:
  • Syntax: =SUM(LN(NORM.DIST(x_range, mean, stdev, FALSE)))
  • Example: Requires setting up a range of x values, mean, and standard deviation.
  1. Spearman's Footrule Distance:
  • Syntax: Requires manually computing the sum of absolute differences between the ranks of corresponding values in two samples.
  • Example: Compute the Spearman's footrule distance by summing the absolute differences between the ranks.
  1. Gamma Distribution Probability:
  • Syntax: =GAMMA.DIST(x, alpha, beta, [cumulative])
  • Example: =GAMMA.DIST(3, 2, 1, FALSE)
  • Calculates the probability of a gamma distribution.
  1. Log-Likelihood for Maximum Likelihood Estimation (MLE):
  • Syntax: Requires constructing the likelihood function and maximizing it for parameter estimation.
  • Example: Set up the likelihood function and optimize it for maximum likelihood estimation.
  1. Weighted Average:
  • Syntax: =SUMPRODUCT(values, weights)/SUM(weights)
  • Example: =SUMPRODUCT(A1:A10, B1:B10)/SUM(B1:B10)
  • Computes the weighted average of a dataset.
  1. Multinomial Distribution Probability:
  • Syntax: =MULTINOM.DIST(observed_range, trials, probabilities, [cumulative])
  • Example: =MULTINOM.DIST(A1:A3, 10, {0.2,0.3,0.5}, FALSE)
  • Calculates the probability of a multinomial distribution.
  1. Poisson Process Probability:
  • Syntax: =EXPON.DIST(x, rate, [cumulative])
  • Example: =EXPON.DIST(5, 2, FALSE)
  • Calculates the probability of a Poisson process.
  1. Log-Logistic Distribution Probability:
  • Syntax: =LOGLNORM.DIST(x, mean, stdev, [cumulative])
  • Example: =LOGLNORM.DIST(10, 8, 2, FALSE)
  • Computes the probability of a log-logistic distribution.
  1. Exponential Decay:
  • Syntax: =EXP(-lambda * x)
  • Example: =EXP(-0.05 * 10)
  • Models exponential decay over time.
  1. Geometric Distribution Probability:
  • Syntax: =GEOM.DIST(x, probability, [cumulative])
  • Example: =GEOM.DIST(3, 0.2, FALSE)
  • Calculates the probability of a geometric distribution.
  1. Rectangular Distribution Probability:
  • Syntax: =UNIFORM.DIST(x, a, b, [cumulative])
  • Example: =UNIFORM.DIST(7, 5, 10, FALSE)
  • Computes the probability of a rectangular distribution.
  1. Gumbel Distribution Probability:
  • Syntax: =GUMBEL.DIST(x, alpha, beta, [cumulative])
  • Example: =GUMBEL.DIST(15, 10, 3, FALSE)
  • Calculates the probability of a Gumbel distribution.
  1. Chi-Square Goodness of Fit Test:
  • Syntax: =CHISQ.DIST.RT(chisquare_stat, degrees_freedom)
  • Example: Requires computing the chi-square statistic and applying the CHISQ.DIST.RT function.
  1. Fisher-Snedecor Distribution Probability:
  • Syntax: =FDIST(x, degrees_freedom1, degrees_freedom2, [cumulative])
  • Example: =FDIST(2.5, 3, 5, FALSE)
  • Computes the probability of a Fisher-Snedecor distribution.
  1. Weibull Probability Plot:
  • Syntax: Requires plotting the data points against the corresponding quantiles of a Weibull distribution.
  • Example: Create a scatter plot with the data points and theoretical quantiles.
  1. Zero-Inflated Poisson Distribution Probability:
  • Syntax: =ZIP.DIST(x, lambda, probability_zero)
  • Example: =ZIP.DIST(2, 1.5, 0.2)
  • Calculates the probability of a zero-inflated Poisson distribution

  • .Kernel Density Estimation (KDE):
  • Syntax: Requires creating a kernel density plot in Excel using external tools or add-ins.

  • Time Series Decomposition:
  • Syntax: Requires using tools like Data Analysis ToolPak or external time series analysis software for decomposition.

  • Multinomial Distribution Probability:
  • Syntax: =MULTINOM.DIST(x1, x2, ..., probability_range)
  • Example: =MULTINOM.DIST(2, 3, 4, {0.1, 0.2, 0.3, 0.4, 0.5})

  • Calculates the probability of a multinomial distribution.

  • Empirical Cumulative Distribution Function (ECDF):

  • Syntax: Requires creating a step function of the sorted data.

  • Stochastic Processes Simulation:

  • Syntax: Requires using Excel's random number generation functions for simulating stochastic processes.

  • These additional formulas cover topics like kernel density estimation, time series decomposition, multinomial distribution, empirical cumulative distribution function, and stochastic processes simulation in Excel.

Comments

Post a Comment

Popular posts from this blog

Lila's Journey to Becoming a Data Scientist: Her Working Approach on the First Task

-
  Education and Skill Acquisition: Lila, with an economics background, decides to transition to data science. Enrolls in the IBM Data Science Professional Certificate for comprehensive training. Gains proficiency in statistics, machine learning, and programming languages. Building a Strong Foundation: Develops a deep understanding of data manipulation and visualization using Python libraries. Visualization for Storytelling: Learns to create informative visualizations, aiding in effective communication of findings. Hands-On Experience: Engages in Kaggle competitions and personal data projects for practical experience. Establishes a GitHub profile to showcase her projects and skills. Data Wrangling and Preprocessing: Focuses on data cleaning, preprocessing, and handling missing data and outliers. Communication and Storytelling: Hones data storytelling skills using tools like Matplotlib and Plotly. Networking and Collaboration: Actively participates in data science communities, attend...
Read more

Notes on Hiring for Data Science Teams

-
  Unicorn Myth in Hiring: Companies often seek individuals with a myriad of skills for data science roles. Ideal candidates possess domain-specific knowledge, excel in data analysis, and have strong presentation and storytelling skills. However, finding such a "unicorn" is rare, and the focus should shift to other essential qualities. Resonance with Company DNA: The key is to identify applicants who resonate with the company's values and business domain. While analytics skills can be taught, passion for the business is intrinsic and contributes significantly to productivity. Curiosity Matters: Curiosity is a top priority in hiring for a data science team. Candidates should demonstrate a broad curiosity, showing interest in various aspects of their surroundings beyond just data science. Sense of Humor: A lighthearted approach is crucial, as being too serious may hinder collaboration and creativity. A sense of humor aids in navigating challenges and fosters a positive work ...
Read more

switch functions

-
  Nested IF Function : The  nested IF  function allows you to perform tests with multiple conditions and return different values based on the results of those tests. It is called “nested” because one IF function is embedded inside another IF function. Syntax of a generic nested IF formula: =IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4) ) ) Each subsequent IF function is embedded into the  value_if_false  argument of the previous function. SWITCH Function : The  SWITCH  function is a more compact alternative to nested IF statements. It compares an expression against a list of values and returns the result according to the first matching value. Syntax of the SWITCH function: =SWITCH(expression, value1, result1, [default or value2, result2], ... [default or valueN, resultN]) It has four arguments: expression : The value to compare against the list of values. valueN : A value com...
Read more