Calculating the Standard Error of the Mean for each averaged value.

This is not straight forward because there isn't a pre-made formula for this calculation. Therefore a formula will need to be written in to the formula bar.

Steps for creating a formula in the formula bar include

1. Select the cell in to which the calculated SEM value will be displayed.
2. With that cell selected, use the mouse to place the cursor in the formula bar.
3. Type an equal sign (=) at the beginning of the text box to signify that a formula and not a value is to be placed in that cell.
4. The formula for the SEM is the standard deviation divided by the square root of the number of samples being used to calculate the average. An example of the formula for a calculation where the standard deviation is in cell C20 and the number of samples is 10, would look like this:
   • C20/(SQRT(10))
5. After you've entered the formula, hit the Enter/Return key. The value of the calculation will show up in the cell.

 

Video

• AVI format (for those using Windows Media player - also has text captions)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)
• WMV format (fastest Download requires Windows Media Player)

 

Descriptive statistics: Calculating the Standard Deviation.

As when calculating the average e premade functions can be used. Remember that the most common error is selecting the wrong cells to average.

To calculate the standard deviation, Excel wants to know

1. In which cell should the calculated number appear. The calculation should generally be placed in a cell that is consistent with the data that is being used to calculate the standard deviation. See "Moving around and selecting specific cells" for info on how to select a particular cell.
   
2. What sort of calculation needs to be done.
• Select fx in front of the Formula bar and then select the appropriate function (STDEV) from either the Most Recently Used or the Statistical category.
  or
• Select Function from the Insert menu at the top of the window and then select the appropriate function (STDEV) from either the Most Recently Used or the Statistical category.
  
3. Which cells should be part of the calculation. After selecting the type of function, a box will be made available which will let you either enter the range of cells manually or select them from the data sheet using the mouse.

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

 

Descriptive statistics: Calculating the Average.

There are premade functions for many of the common calculations and comparisons, including calculating the average. The biggest error users make is selecting the wrong cells to average. For instance, in our case it makes sense to average the yield from all 10 plants within a single location on a particular day, but it does not make sense to average the yield of plant 1 from 2 different locations on a particular day. Be sure of what data is being used to calculate the average!

To calculate the average, Excel wants to know

1. In which cell should the calculated number appear. The calculation should generally be placed in a cell that is consistent with the data that is being used to calculate the standard deviation. See "Moving around and selecting specific cells" for info on how to select a particular cell.
   
2. What sort of calculation needs to be done.
• Select fx in front of the Formula bar and then select the appropriate function (AVG) from either the Most Recently Used or the Statistical category.
  or
• Select Function from the Insert menu at the top of the window and then select the appropriate function (AVG) from either the Most Recently Used or the Statistical category.
  
3. Which cells should be part of the calculation. After selecting the type of function, a box will be made available which will let you either enter the range of cells manually or select them from the data sheet using the mouse.

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

 

Adding data to the spread sheet

This process is rather straight forward. The only problem is replication. Be sure to double check that the entries from the original sheet are correct and in the proper place in the Excel spreadsheet.

Data transfer errors have lead to many a lost hour and even incorrect results!

To add data, select the appropriate cell and type in the number. If you seem to be stuck, press the ESC key.

Video - none needed

 

 

Your spread sheet should be set up so that it has all the relevant information that one would need to understand the experiment and the data.

A good rule of thumb is to imagine returning to the spreadsheet after 1 year. What information would be necessary for you to know what is being displayed? If you resort to saying "I'll remember that" you will be in big trouble upon reinvestigating the spreadsheet.

1. Add any information about the experiment on a separate sheet or at the top of the raw data sheet.
2. Add information about what is represented in each column and each row. Generally this will replicate the datasheet you used to collect the original data. Leave an extra few rows at the top and 1 row to the left for notes.
3. Add a key that describes the experiment and what the different header information represents ( the ones you left blank in step 2).
4. Place borders around certain cells or lines under cells.
5. Color code some of the areas in your spread sheet and, if necessary, type in to one of the cells what each color is meant to represent.
6. Leave a few cells after each row and each column so that the results of any calculations have a place to be stored.

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

Moving around the spread sheet (selecting specific cells) can be done in many ways.

Clicking on a particular cell with the mouse will select a particular cell. This is the easiest and most used method for moving around your spreadsheet. However, it is sometimes convenient to be able to keep your hands on the keyboard. For those occasions, other mechanism are available.

1. Since each cell is represented by a column letter and a row number, a cell reference can be typed directly in to the Name Box which is found at the bottom left of the top menu bars.
   
2. The arrow keys can be used to move in the direction that the key is pointing.
   
3. The Enter/Return and the tab key will change the selected cell one below or to the right, respectively. Using the shift key in conjunction with the Enter/Return or tab key will move the selected cell in the opposite direction.
   

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

 

WHAT IS EXCEL?

Excel is a spreadsheet program which can

• store data
• create graphs/charts
• provides set functions for doing descriptive statistics as well as some comparative statistics and
• allow for user defined calculations
• be used as a 2D CAD program.

In any case it is important to document everything that is put in to the spreadsheet so that it will be clear what each entry/graph/chart, calculation and figure represent. This is particularly important if someone else is looking at the spreadsheet or you need to revisit the data after a few months. It is amazing how much can be forgotten in a month!

 

Creating a bar chart of averaged data

Creating a bar chart of averaged data is a common mechanism for illustrating the differences between data collected under different conditions.

In our case the different condition is the location in which the plants were grown.

To create a bar graph of this data:

• The first item to note is that Excel refers to Graphs as Charts.
• Select Chart from the Insert Menu at the top of the window.
• Select Column from the "Chart type" Column.
• Select the appropriate sub-type. Generally the first one is the best although the 3-D effect does give a feeling of knowing what your doing.
• Click Next>
• Excel will attempt to determine the data that is appropriate for the graph. If the selection is incorrect either
  1. Type in the appropriate range (not recommended).
     or
  2. Select the data icon at the far right of the Data Range: text box and the use the mouse to highlight the data that is to be graphed.
• By looking at the preview graph and toggling between the Rows and Columns settings, it will be obvious which works best. When the graph looks correct, click on the Next> button.
• In the Chart Options window be sure to add the appropriate "Titles." Then click on Next>
• In the Chart Location window select "As new sheet" for the Chart Placement. This will leave the data sheet less cluttered and easier to read.
• Click Finish!

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

 

Adding the standard error of the mean (SEM) bars to a bar chart of averaged data.

1. This will need to be done 1 line or 1 set of bars at a time.
2. Using the graph above as an example, double click on a particular bar e.g. the yellow bar. A square should appear in each of the similarly colored bars. In some cases the bar or line will need to first be selected by single clicking on the appropriate line or bar before double clicking. If the edges of the bar are highlighted click the esc key before selecting the appropriate bar.
3. The following window should appear:

1. In the "Format Data Series" window
   1. Select the Y Error Bars tab. If you only see a window with two tabs (Patterns & Fonts), go back to step 2 and select a line or bar.Double click in the same place.
   2. Select the manner in which the error bars should be displayed.
   3. Select "Custom" error amount - You will need to have already calculated the SEM and the values for the SEM should occupy a column somewhere in your spreadsheet. If you haven't done this already see "Calculating the Standard Error of the Mean (SEM)"
   4. Use the spreadsheet shortcut to select the appropriate column (blue circle)
2. When finished click the [OK] button.
3. Repeat for the other series.

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

 

General consideration before creating a chart/graph.

Be sure you know what you want to say with your chart/graph.

In the case of tomatoes on a tomato plant, we don't want to show the average yield of location A on the same graph as the individual yield of Plant 4 across all 3 days. This makes no sense. On the other hand we may want to show the change in average yield between June 1 and Aug 1 for both locations. Presenting them side by side would provide the reader with a quick way of assessing the yields as a function of location and of time.

See subsequent sections for video tutorial

 

Adjusting an already created chart (still being worked on).

Adjusting your chart is probably the trickiest aspect of working with Excel. The directions below relate to this graph.

I. Changing the range of the different axis.

• Click on the axis of interest. The line should end up being selected.
• Double click once you have selected the appropriate line.
• If the y axis line was selected, the following should appear:
• Make the appropriate adjustments and click OK

II. Setting or changing Titles & Legends

• Right click on the chart/graph and select "Chart Options"
• The "Chart Options" window will open up. In this window many global aspects of the graph can be changed or made to appear of disappear. For instance, if the x or y axis labels or the Legend have disappeared, they can be reinstated from this window.
• We will focus on Titles and Legend.

 

• TITLES - adjusting the titles can be done directly in the graph but there are times when titles disappear. This window will allow the user to recreate the titles for the Chart, x-axis and y-axis. If there is a secondary axis (usually along the right side of the chart), this is where one would set text describing this axis. The window shold look like this:

 

• LEGENDS - legends are located in the box to the right of the graph. They can be moved in toto from the chart view (without actually going into any options window). From the chart view the text can also be changed. However, it there is no legend, the Chart Options window will need to be opened.
  • Click on the Legend tab
  • Click on the Show legend (or uncheck if you don't want the course to appear)
  • Select the appropriate placement. This can be changed later.
  • Click OK

III. Formating the text in your chart

• Click on the text that you want to modify.
• Right click with in the box.
• Select "Format ..."
• Adjust the font and click OK.
  
• To format the text in the x and y axis e.g. the A and B which label the x axis, you will need to access the "Format Axis" window. See section I above for directions on how to do this.
• In the "Format Axis" window click on the Font tab
• From this window make the appropriate adjustments. If you're not getting the results you desire, try unchecking the Auto scale feature.

Video - in preparation

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

 

Moving Data and Charts to Word.

The data and charts can be moved in to Word in many different ways. This is particularly true if you are using a Windows machine. The illustration below is done on a windows machine.

• To move data/charts from Excel to Word.
  1. In Excel highlight the area of interest.
  2. Edit>Copy
  3. Open a document in Word
  4. In Word, move the cursor to where you want the Excel sheet to show up.
  5. Select Paste from the Edit window.

  Video AVI format / WMV format / RM format / MOV format - in preparation

 

Charting/Graphing a timeline.

The video illustrates how a timeline would be made. This sort of graph is best when there are a number of values calculated across many days. For instance, the number of tomatoes harvested on each day from different plants as measured throughout the year.

Since there are only 3 timepoints, a bar graph would probably be best for displaying changes over time, none-the-less this section will still use the same data to demonstrate the construction of a line graph.

• The first item to note is that Excel refers to Graphs as Charts.
• Select Chart from the Insert Menu at the top of the window.
• Select Line from the "Chart type" Column.
• Select the appropriate sub-type. Generally the first one is the best.
• Click Next>
• Excel will attempt to determine the data that is appropriate for the graph. If the selection is incorrect either
  1. Type in the appropriate range (not recommended).
     or
  2. Select the data icon at the far right of the Data Range: text box and the use the mouse to highlight the data that is to be graphed.
• By looking at the preview graph and toggling between the Rows and Columns settings, it will be obvious which works best. When the graph looks correct, click on the Next button.
• In the Chart Options window be sure to add the appropriate "Titles." Then click on Next>
• In the Chart Location window select "As new sheet" for the Chart Placement. This will leave the data sheet less cluttered and easier to read.
• Click Finish!

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

Starting a blank spreadsheet and naming sheets

Sheets should be named for easy access to the information contained in a particular sheet. To name a sheet double click on the name of the sheet and type in the appropriate word or phrase. Shorter labels are best because they can be seen quickly.

Video

• AVI format (for those using Windows Media player)
• RM format (for those using RealOne player - There will be 2 windows before the video shows up. Click Cancel in the first and Exit in the second. The video should then play.)
• MOV format (for those using Quicktime)

Original Data

[ Instructions ] [ Original Data Sheet ][ Final Excel Document]