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 will be asked to post a comment at some point during the semester briefly summarizing a spreadsheet function or technique (which will earn you a point towards your course participation grade).
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.
=COUNT(data): returns number of numeric values in the range of cells “data”
=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.
=Frequency(data, classes): Calculates the frequency distribution of a one-column array into specified classes.
=SUM(data): Returns the sum of a series of numbers and/or cells.
=average(data): Returns the numerical average value in a dataset
=MODE(data): Returns the most commonly occurring value in a dataset.
The MEDIAN function returns the median (middle number) in a group of supplied numbers. For example:
=MEDIAN(1,2,3,4,5) returns 3.
=QUARTILE(date, quartile_number): Returns a value nearest to a specified quartile of a dataset.
=SQRT( then type a number of your choice.): to square root, value must be positive.
=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.
=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
=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”.
=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
=FACT(n)
returns the factorial of a number
(Ex: =FACT(5) will return 5! = 5 * 4 * 3 * 2 * 1 = 120)
=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
=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).
=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.