<< Chapter < Page Chapter >> Page >
Mean =average(beginning cell address:end Cell address)
Median =meadian(beginning cell address:end Cell address)
Mode* =mode(beginning cell address:end Cell address)
Largest number =large((beginning cell address:end Cell address,1)
Smallest number =small(beginning cell address:end Cell address,1)
Range =largest number-smallest number
Standard deviation =stdev(beginning cell address:end Cell address)
1 st quartile =quartile(beginning cell address:end Cell address,1)
2 nd quartile =quartile(beginning cell address:end Cell address,2)
3 rd quartile =quartile(beginning cell address:end Cell address,3)
IQR =3 rd quartile=1 st quartile
Percentile (eg. 90 th percentile) =percentile(beginning cell address:end Cell address,the percentile between 0-1)Eg =percentile(beginning cell address:end Cell address, .9)
Z score =(x – mean)/standard deviation

These few formula will allow you to calculate the 5 number summary or the relevant data for normal or nearly normal data.

To use the above formulas to create summary statistics you will use one column for your labels and one column for placing your equations. Below are screenshots of how to create your descriptive statistics. The Googlespreadsheet document shows the equations. The excel document show the results of those equations.

*Multiple Modes: In Google Spreadsheet the mode function will not provide multiple modes. In Google Spreadsheet only the mode of the one number (the largest number, see example below) will be provided if there are multiple modes. In Excel you also only see one number based on its “rank” in your list unless you highlight the cell with your MODE.MULT formula (as demonstrated below) and press F9 on your keyboard. You will then see your multiple modes. Note that once you left that cell, the only way you will see your multiple modes is in the formula bar as the screenshot indicates. View the screen shot below. The first screenshot is with the multiple mode formula highlighted. The second screenshot is after pressing F9. on the keyboard. The third screenshot displays the formulas for a set of data in Google Spreadsheet.

To use the above formulas to create summary statistics you will use one column for your labels and one column for placing your equations. Below is a screenshot of one example of how to create your descriptive statistics. The Googlespreadsheet document shows the equations. The excel document show the results of those equations.

Optional classroom exercise:

At your computer, try this exercise: (1) Open the file, Statistics First Day of Class Survey that you worked on previously (2) open the file in Excel or Google Spreadsheet (3) create a new worksheet tab and label it Descriptive Statistics; (4) pick column of data that is quantitative and has been “cleaned” and create a the descriptive statistics with appropriate titles, labels, and lines; (4) Based on your descriptive statistics, determine if you will create a historgram or a box plot of the data (5) create the appropriate graph (6) compy your descriptive statistics and your graph into a word document and describe the data and tell the story about the shape, center, spread of your data. (7) save the Excel or Google Spreadsheet file again and this time post only the word document you created in the appropriate Moodle assignment.

Using the data analysis in excel to create descriptive statistics for quantitative data:

When one is using Excel and one only has quantitative data, there is another option to create a chart of the descriptive statistics. On the Excel Ribbon, if you choose the “DATA” tab, you will see Data Analysis as one of the menu items. If you double click on “data analysis”, a popup menu will appear. The options are in alphabetical order and you will be looking for Descriptive statistics. If you click once on descriptive statistics to highlight it, and mouse over and click on the “OK” button, another popup menu will appear. Here you will enter your input range which is the beginning and end cell address for your data including your label in the first row, put a check mark in the “Labels in the First Row” box, and a check mark in the “Summary statistics” box. You have one more choice left. Do you what you descriptive statistics on the same spreadsheet that you are working on or do you want to put the descriptive statistics on a separate worksheet. If you want the descriptive statistics on a separate worksheet click on the radial button in front of New Worksheet. If you want the descriptive statistics chart in the same worksheet click on the radial button in from of “Output Range” and then you will need to put in an empty cell address in the box provided by entering it or placing your curser in the box provided and then clicking on a single empty cell on your spreadsheet. Next, click on the “OK” button. Voila, descriptive statistics will appear. See images below. Once again without using =MODE.MULT the descriptive statistics only gives one of the modes from this data set.

Questions & Answers

Application of nanotechnology in medicine
what is variations in raman spectra for nanomaterials
Jyoti Reply
I only see partial conversation and what's the question here!
Crow Reply
what about nanotechnology for water purification
RAW Reply
please someone correct me if I'm wrong but I think one can use nanoparticles, specially silver nanoparticles for water treatment.
yes that's correct
I think
what is the stm
Brian Reply
is there industrial application of fullrenes. What is the method to prepare fullrene on large scale.?
industrial application...? mmm I think on the medical side as drug carrier, but you should go deeper on your research, I may be wrong
How we are making nano material?
what is a peer
What is meant by 'nano scale'?
What is STMs full form?
scanning tunneling microscope
how nano science is used for hydrophobicity
Do u think that Graphene and Fullrene fiber can be used to make Air Plane body structure the lightest and strongest. Rafiq
what is differents between GO and RGO?
what is simplest way to understand the applications of nano robots used to detect the cancer affected cell of human body.? How this robot is carried to required site of body cell.? what will be the carrier material and how can be detected that correct delivery of drug is done Rafiq
analytical skills graphene is prepared to kill any type viruses .
what is Nano technology ?
Bob Reply
write examples of Nano molecule?
The nanotechnology is as new science, to scale nanometric
nanotechnology is the study, desing, synthesis, manipulation and application of materials and functional systems through control of matter at nanoscale
Is there any normative that regulates the use of silver nanoparticles?
Damian Reply
what king of growth are you checking .?
What fields keep nano created devices from performing or assimulating ? Magnetic fields ? Are do they assimilate ?
Stoney Reply
why we need to study biomolecules, molecular biology in nanotechnology?
Adin Reply
yes I'm doing my masters in nanotechnology, we are being studying all these domains as well..
what school?
biomolecules are e building blocks of every organics and inorganic materials.
anyone know any internet site where one can find nanotechnology papers?
Damian Reply
sciencedirect big data base
Introduction about quantum dots in nanotechnology
Praveena Reply
what does nano mean?
Anassong Reply
nano basically means 10^(-9). nanometer is a unit to measure length.
how did you get the value of 2000N.What calculations are needed to arrive at it
Smarajit Reply
Privacy Information Security Software Version 1.1a
Got questions? Join the online conversation and get instant answers!
Jobilize.com Reply

Get the best Algebra and trigonometry course in your pocket!

Source:  OpenStax, Collaborative statistics using spreadsheets. OpenStax CNX. Jan 05, 2016 Download for free at http://legacy.cnx.org/content/col11521/1.23
Google Play and the Google Play logo are trademarks of Google Inc.

Notification Switch

Would you like to follow the 'Collaborative statistics using spreadsheets' conversation and receive update notifications?