We will use the comments to 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 briefly summarizing a spreadsheet function or technique at sometime during the semester (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 (see my first comment below for an 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(B2:B100)
In the context of statistics, we often use =COUNT to find the “sample size,” i.e., the number of observations/values in a given dataset.
=max(first value, last value)
This function provides the maximum value of a set of numbers.
According to information provided by the function: =max provides the “maximum value in a numeric dataset.”
=min(first value, last value)
This function provides the minimum value of a set of numbers.
According to the information provided by the function: =min provides the “minimum value in a numeric dataset.”
=sort(a2:a41)
=sort allows for the data in ascending or descending order. In this case the data is sorted in ascending which is default.
=SUM is used to find the sum of a series of numbers and/or cells
=average(data): is the function that calculates the mean/average of the data by using the sum of all of the values divided over the number of values in a given dataset.
“=MEDIAN(data)”: The median is the value separating the higher half of a data sample, a population, or a probability distribution, from the lower half. In simple terms, it may be thought of as the “middle” value of a data set
=QUARTILE(data, quartile_number), Returns a value nearest to a specified quartile of a dataset.
=FREQUENCY(data, class_endpoints): Calculates the frequency distribution of a one-column array (“data”) into specified classes, where the class intervals are defined by the list of “class_endpoints”.
The output of FREQUENCY will be a vertical list of frequencies of length one greater than “class_endpoints” since the final value is the number of elements in data greater than the last class endpoint.
Note that =frequency uses the “right-endpoint inclusion convention” to define the class intervals based on the list of class_endpoints; this is illustrated in the following example:
Example: =FREQUENCY(A2:A40,B2:B5) will output a list of 5 class frequencies for the data set in A2:A40, where:
–the first frequency will be the number of data points x in A2:A40 such that x ≤ B2
–the second frequency will the number of data points x such that B2 < x ≤ B3
–the third frequency will the number of data points x such that B3 < x ≤ B4
–the fourth frequency will the number of data points x such that B4 < x ≤ B5
–the fifth frequency will the number of data points x such that B5 < x
=PERCENTILE(data, percentile): Returns the value at a given percentile of a dataset.
=sqrt(data)
Return positive square root of positive number.
=VAR(DATA)
Calculates the variance based on the sample.
=STDEV(ORIGINAL DATASET)
This calculates the standard deviation of the sample.
=VARP(data)
Calculates the variance based on an entire population.
Example: =VARP(A2:A10)
=STDEVP(data)
Calculates the standard deviation based on an entire population.
Example: =STDEVP(A2:A10)
=CORREL
Calculates r, the Pearson product-moment correlation coefficient of a dataset.
EXAMPLE
CORREL(A2:A100, B2:B100)
=SLOPE(data_y, data_x) calculates the slope of the line resulting from linear regression of a dataset.
ex:
SLOPE(A2:A100, B2:B100)
=INTERCEPT(data_y, data_x) calculates the y-value at which the line resulting from linear regression of a dataset will intersect the y-axis (x=0).
ex:
INTERCEPT(A2:A100, B2:B100)
=fact(n)
outputs n! (n factorial)
=permut(n,k)
outputs number of permutations of length k taken from n objects
=combin
calculates the number of combinations without repetitions for a given number of items.