Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Using Excel for Data Analysis: Tips on Data Entry, Functions, Bar Charts, and Box Plots, Exercises of Statistics

University of Adelaide (UA)Statistics

Instructions on using Microsoft Excel for data analysis, including data entry tips, common functions, creating bar charts, and making box plots. It covers calculating means, standard deviations, and standard errors, as well as sorting and formatting charts.

Typology: Exercises

2021/2022

Uploaded on 07/05/2022

lee_95
lee_95 🇦🇺

4.6

(59)

999 documents

1 / 10

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
Welcome to the wonderful world of EXCEL!
You can use the same copy, paste, cut shortcut keys that you use in Microsoft Word in EXCEL, or mouse right click.
Contol-x: cut
Control-c: copy
Control-p: paste
When dealing with data:
Always double check your numbers after you enter the data.
If you see a value that looks really weird, try to see if there was a mistake or if there is a reason for an extremely
high or low value (so if you see a caterpillar value that looks ‘off’ ask the person whose caterpillar it is.)
Keep one copy of your raw data untouched, copy the values to another worksheet and work with the data there.
Functions: in EXCEL anytime you type ‘=’ into a cell then EXCEL expects a function or formula to follow. EXCEL can
calculate hundreds of formulas/function. To search for a specific function (or browse them all for fun), click on the
formula tab at the top of your screen.
A few common functions:
To calculate a column or row mean:
o =average(highlight row or column that you want to average)
o Example: To calculate the average of all the values A1 – A25, =average(A1:A25)
To calculate the sum of all values: =sum(A1:A21)
To count all the values (i.e. determine sample size): =count(A1:A25)
To calculate standard deviation =stdev.s (range of values)
To calculate a square root: =sqrt(value)
1. Copy the raw data into another worksheet (this way if you inadvertently delete or change a data value, you still
have a clean copy of your data).
2. Sort the data based on treatment type.
3. Optional: Cut and paste the data into separate treatment columns.
4. Calculate the average and standard deviation for each treatment group.
EXCEL does not have a standard error function. So you’ll have to calculate standard deviation and then use that to
calculate standard error (standard error = standard deviation / square root of the sample size).
pf3
pf4
pf5
pf8
pf9
pfa

Partial preview of the text

Download Using Excel for Data Analysis: Tips on Data Entry, Functions, Bar Charts, and Box Plots and more Exercises Statistics in PDF only on Docsity!

Welcome to the wonderful world of EXCEL!

You can use the same copy, paste, cut shortcut keys that you use in Microsoft Word in EXCEL, or mouse right click.  Contol-x: cut  Control-c: copy  Control-p: paste

When dealing with data:  Always double check your numbers after you enter the data.  If you see a value that looks really weird, try to see if there was a mistake or if there is a reason for an extremely high or low value (so if you see a caterpillar value that looks ‘off’ ask the person whose caterpillar it is.)  Keep one copy of your raw data untouched, copy the values to another worksheet and work with the data there.

Functions : in EXCEL anytime you type ‘=’ into a cell then EXCEL expects a function or formula to follow. EXCEL can calculate hundreds of formulas/function. To search for a specific function (or browse them all for fun), click on the formula tab at the top of your screen.

A few common functions:  To calculate a column or row mean: o =average(highlight row or column that you want to average) o Example: To calculate the average of all the values A1 – A25, =average(A1:A25)  To calculate the sum of all values: =sum(A1:A21)  To count all the values (i.e. determine sample size): =count(A1:A25)  To calculate standard deviation =stdev.s (range of values)  To calculate a square root: =sqrt(value)

  1. Copy the raw data into another worksheet (this way if you inadvertently delete or change a data value, you still have a clean copy of your data).
  2. Sort the data based on treatment type.
  3. Optional: Cut and paste the data into separate treatment columns.
  4. Calculate the average and standard deviation for each treatment group.

EXCEL does not have a standard error function. So you’ll have to calculate standard deviation and then use that to calculate standard error (standard error = standard deviation / square root of the sample size).

Making beautiful bar charts in EXCEL

 Please note that the numbers used in this handout are from a previous class, so your numbers will be different.  These instructions are for Microsoft Office 365 using a PC.

  1. Sort your data so you can easily calculate the appropriate averages (data tab, highlight data, sort by treatment).
  2. Calculate the average, standard deviation, and sample size for each category. I like to setup the summary statistics in a ‘table’ that I make myself.
  3. Calculate standard error: standard error = stdev.s(values)/sqrt(sample size)
  4. Once you have done this for both treatments, then highlight the values for your bar chart. In this example, the height of the bars will be equal to the mean values of the fertilizer and control treatment.
  5. Click on the ‘insert’ tab at the top of the screen.
  6. In the ‘charts section’ in the middle, click on ‘columns’. Select a 2D column. The point of having a figure is to visually describe your data. 3D graphics don’t help. Ridiculous colors don’t help. Patterns don’t help. Weird shapes don’t help. Keep it simple.
  1. If you can’t see the x or y-axis lines, then you need to change their color. Right click on the axis. Select “Format axis”. Select the paint bucket icon. Select “Line”. Choose “Solid line” and then select a color.
  2. I really don’t like gridlines (the horizontal bars across the chart area), so please delete them in my class (right click on them so they are highlighted and hit delete.
  3. Check font size. If you want to change font size/type right click on the chart and then change the font size/type. I typically go with 12-point font, not-bolded. It depends on what size your figure will be in the final product.
  4. Delete the “chart title” you do not need a chart title for your lab reports.

Hopefully you now have a chart that looks something like this!

  1. Time to add standard error bars. You do not want to use the standard error bars automatically calculated by EXCEL. Click on your chart. Within ‘Chart Tools’ section at the top of the screen select the ‘Design’ tab. Click on ‘Add chart elements’ on the far left. Select “Error bars”. Select ‘More Error Bar Options’ (bottom of the list).
  2. The ‘Format Error Bars’ box will appear. Select Custom, click on ‘specify value’. The ‘custum error bar’ box will appear. With that box, click on the select range icon under the postive error value (this is the part of the error bar going up) and highlight the standard error values for both treatments. Repeat for the negative error value, the numbers will be the exact same. Click OK. You may need to move the ‘format error bars’ and ‘custom error bar’ box to the side in order to highlight your standard error values.
  3. Check to make sure the standard error values you calculated in the table match the value of the bars. If not, you may have highlighted the wrong numbers.
  1. Highlight the whole table, including all labels, then select the Insert tab at the top of the screen. Under Charts select a Line chart and choose the Line with Markers option. The results won’t look anything like a box plot.
  2. Right click on the chart, choose Select Data and click on Switch Row/Column. Select OK.
  1. Within the Chart Tools tab, select Format. Show the Format task pane by clicking on the Format selection button in the far left corner. Right-click on the top line. Select the paint bucket in the Format task pane. Select no line. Then the line connecting the two points will disappear. Repeat for the other four data series.
  2. Within the Chart Design tab select Add Chart Elements and add Up/Down bars.
  3. Again within the Chart Design tab select Add Chart Elements, Lines and add High-Low lines.

t -tests in EXCEL You will need to install the Data Analysis tookpak (it is an add-in with EXCEL).

  1. Install the data analysis toolpak: File > Options > Add-Ins > Manage Excel Add-in (Go), Analysis Toolpak (OK).
  2. Select the data tab and then the data analysis icon. Select t-test, 2-sample assuming equal variances.
  3. In the Variable 1 Range, highlight all of the fertilizer values. In the Variable 2 Range, highlight all of the control values. In the output range, highlight where you want EXCEL to put the output.
  4. Examine the results. Check to make the mean and the observations number are correct. EXCEL provides you with both the one-tail and two-tail p-values. For this experiment, you use a two-tail p-value.

Write up your results (one sentence) with the statistics as you would in the results section of your lab report: