By Stephen Nelson
Want to work with fisher transformations or frequency distributions? Microsoft Excel supplies some useful functions, including FISHER, FISHERINV and FREQUENCY.
Fisher Transformations using the FISHER and FISHERINV Functions
The Fisher’s z’ transformation converts Pearson’s r to the normally distributed variable z’.
You use the Fisher’s z’ to compute confidence intervals on Pearson’s correlation and on the difference between correlations.
The FISHER function computes from r to z’. The function has the following syntax:
The FISHERINV function computes from z’ to r. The function has the following syntax:
Frequency Distributions using the FREQUENCY Function
The FREQUENCY function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, you must enter it as an array formula. The frequency formula uses the following syntax:
=FREQUENCY (data array, bins array)
where data array is an array of values for which you want to count frequencies and bins array is an array of bins (listed in ascending order and as upper limits) into which you want to group the values in the data array.
For example, if you wanted to create a frequency distribution using data array stored in B2:B21, a bins array stored in C2:C6 and you wanted to output your frequency distribution to the worksheet range D2:D6, you would select the range D2:D6, then enter:
and press Ctrl+Shift+Enter. When Excel constructs the frequency distribution, it counts the number of items with values less than or equal to the upper limit of the each bin.
About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.
Want to read more about MS Excel tips and tutorials? Visit Hot Excel.