Statistical functions countifs, expondist, forecast, frequency, gammadist

Excel Function COUNTIFS

The COUNTIFS() function is used to calculate the cell count that matches more than one criteria.

This function is similar to COUNTIF() except that, it can take more than one condition and range.

The Syntax is as follows:

COUNTIFS(range1,condition1,range2,condition2)

Range1 Range2 COUNTIFS() Formula
Men 100 2 =COUNTIFS(L$4:L$8,"Men",M$4:M$8,">90")
Men 200 1 =COUNTIFS(L$4:L$8,"Men",M$4:M$8,">190")
Men 20 0 =COUNTIFS(L$4:L$8,"Men",M$4:M$8,"=90")
Men 15 3 =COUNTIFS(L$4:L$8,"Men",M$4:M$8,"<50")
Men 1 4 =COUNTIFS(L$4:L$8,"Men",M$4:M$8,"<>200")

 

 

Excel Function EXPONDIST

The EXPONDIST() function returns the value of exponential distribution of a specified value.

The exponential distribution function is a continuous probability distribution function.

The syntax is as follows:

EXPONDIST(x,lambd,[cumulative or probability density function])

The third argument, if TRUE, calculates Cumulative distribution function, if FALSE, its Probability density function.

X Lambda EXPONDIST() Formula
1 1 0.632120558828558 =EXPONDIST(L17,M17,TRUE)
0.8 2 0.403793035989311 =EXPONDIST(L18,M18,FALSE)
1.2 3 8.19711673418777E-02 =EXPONDIST(L19,M19,FALSE)
0.5 4 0.864664716763387 =EXPONDIST(L20,M20,TRUE)
8 5 1 =EXPONDIST(L21,M21,TRUE)

 

 

Excel Function FORECAST

The FORECAST() function is used to predict the future point value on a linear trend line.

The prediction is based on a supplied set of x and y values.

The Syntax of the function is as follows:

FORECAST(x, known_x_values,known_y_values)

excel statistical functions countifs expondist forecast frequency gammadist

Data Known X Known Y FORECAST() Formula
2 1 2 6.9 =FORECAST(L31,$M$31:$M$35,$N$31:$N$35)
2.5 2 6 9.15 =FORECAST(L32,$M$31:$M$35,$N$31:$N$35)
5 3 14 20.4 =FORECAST(L33,$M$31:$M$35,$N$31:$N$35)
7 4 15 29.4 =FORECAST(L34,$M$31:$M$35,$N$31:$N$35)
6 5 20 24.9 =FORECAST(L35,$M$31:$M$35,$N$31:$N$35)

 

 

Excel Function FREQUENCY

The FREQUENCY() function is used to calculate the number of values from a specified array matching certain criteria.

This function deals with array of values.

The syntax is as follows:

FREQUENCY(array of data_array_of_bin)

Data Bins FREQUENCY() Formula
2 14 3 =FREQUENCY(L$46:L$50,M46)
11 11 2 =FREQUENCY(L$46:L$50,M47:M48)
12 17 3 =FREQUENCY(L$46:L$50,M48)
21 26 1 =FREQUENCY(L$46:L$50,M46:M49)
45 4 1 =FREQUENCY(L$46:L$50,M50)

 

 

Excel Function GAMMADIST

The GAMMADIST() function is used to returns gamma distribution of supplied values.

The gamma distribution is used in queuing analysis in probability analysis subject.

The syntax is as follows:

GAMMADIST(x,alpha_value,beta_value,cumulative)

The 'cumulative' is a Boolean value specifying type of distribution.

TRUE- Cumulative distribution function

FALSE-Probability density function

x alpha beta GAMMADIST() Formula
2 14 3 2.11091309487003E-14 =GAMMADIST(L58,M58,N58,TRUE)
13 11 2 2.78885802321884E-02 =GAMMADIST(L59,M59,N59,FALSE)
34 17 3 6.90927484704603E-02 =GAMMADIST(L60,M60,N60,TRUE)
21 26 1 5.55459718267936E-02 =GAMMADIST(L61,M61,N61,FALSE)
45 4 1 1 =GAMMADIST(L62,M62,N62,TRUE)

 

You can find similar Excel Questions and Answer hereunder

1) Conditional formatting with if statement in Excel

2) statistical functions stdev stdeva stdevp stdevpa steyx

3) How to read a value from a cell in vba in Excel

4) Vba list all files in a folder in Excel

5) Calling a macro from another workbook in Excel

6) Userform initialize vs userform show in Excel

7) Write to text file without quotes in vba in Excel

8) statistical functions trend var vara varp varpa

9) Split string into multiple columns in Excel

10) Filtering the value field in a pivot table in Excel

 

Here the previous and next chapter