Statistics & Probability | Instructor: Suman Ganguli

We will use the comments on this post to keep track of the various spreadsheet functions and techniques we will use in class during the semester.

You should post the given spreadsheet function or technique, and give a short summary and/or example of what it does; this can be copied directly (or preferably adapted) from the built-in spreadsheet documentation.

Example:

=COUNT(data): Returns the number of numeric values in a dataset; usually the argument “data” is a range of spreadsheet cells, e.g.

=COUNT(A2:A30)

In the context of statistics, we often use =COUNT to find the “sample size,” i.e., the number of values in a given dataset.

1. Pedro

=COUNT(data): returns number of numeric values in the range of cells “data”

2. Jose A. Jimenez

=MAX(data): Returns the maximum value in a numeric dataset.

EXAMPLE:

=MAX(A2:A100)

Assuming 42 was the higher number from column A from row 2 – 100, the answer would be 42.

=MIN(A2:A100): Returns the minimum value in a numeric dataset.

Assuming 5 was the smaller number from column A from row 2 -100, the answer would be 5.

=SORT(data): This functions sorts the rows of a given array or range by the values in one or more columns.

4. Husam

=Frequency(data, classes): Calculates the frequency distribution of a one-column array into specified classes.

5. JINQUAN LI

=SUM(data): Returns the sum of a series of numbers and/or cells.

6. Ashraf Hossain

=average(data): Returns the numerical average value in a dataset

7. Nicole Lun

=MODE(data): Returns the most commonly occurring value in a dataset.

8. Joan Pujols

The MEDIAN function returns the median (middle number) in a group of supplied numbers. For example:

=MEDIAN(1,2,3,4,5) returns 3.

9. Danyal Ellahi

=QUARTILE(date, quartile_number): Returns a value nearest to a specified quartile of a dataset.

10. Yani acham

=SQRT( then type a number of your choice.): to square root, value must be positive.

11. Mehnaz

=VAR(data) Returns the calculation of variance based on a sample.

=STDEV(data) Returns the calculation of the standard deviation based on a sample.

– Equivalent to the square root of the variance, or SQRT(VAR(…)) using the same dataset.

12. Jasur Khalilov

=CORREL(data_y, data_x) Calculates r, the Pearson product-moment correlation coefficient of a dataset.
-data_y – The range representing the array or matrix of dependent data.
-data_x – The range representing the array or matrix of independent data.
-order that you input the two data sets doesn’t matter

13. Joan Chen

=SLOPE(data_y,data_x):
calculates the slope of the line resulting from linear regression of a dataset.

-Slope measures the steepness of a line, it is calculated as “Rise over Run”.

14. Shannon Russell

=linest(data_y, data_x)

data_y = dependent(Y Values)
data_x = independent (X Values)

The linest function calculates the statistics for a straight line that explains the relationship between the independent and dependent variable

15. Jimmy

=FACT(n)
returns the factorial of a number
(Ex: =FACT(5) will return 5! = 5 * 4 * 3 * 2 * 1 = 120)

16. Justin S

=PERMUT(n, k)

Example: =PERMUT(4, 2)

Returns the number of ways to choose some number of objects (k) from a pool of a given size of objects (n), considering order

17. Sabah Minhaj

=INTERCEPT(data_y, data_x)
data _y= dependent variable
data_x= independent variable
Calculates the y-value at which the line resulting from linear regression of a data set will intersect the y-axis (x=0).

18. Matthew Rivera

=combin(n,k)
n = the size of the pool of objects to choose from.
k = the number of objects to choose.
Example: =combin(4,2)
Returns the number of ways to choose some number of objects from a pool of a given size of objects.