Title: Excel Organization, Identification, and Calculation Syntax
Course: BTEC-2000-301-302-303-F21 > Section: InnovaBio® Project Presentation

Now the fun part is to perform calculations with the data. When asking Excel to do a calculation, type the equal sign (=) to signify you are writing an equation.  To refer to specific values use the position according to the letters at the top of the sheet and the numbers to the right. For example in the image below column B has 72 in the fifth row.  Refer to the cell as “B5.”  A calculation you could perform is adding the first two numbers in the data set. See the caption for more instructions.

To perform an addition calculation,choose an empty cell below or next to the data. Write =B5+B6 and hit enter. In the cell where you typed the equation should have the total of the addition 114.  When you select the cell, you can see the typed formula syntax in the  box above column C which shows actual information typed which is not necessarily the result you see.

One way to determine the total sum for the data set is to click on the cell for each data point and type + between each reference cell. This is still somewhat cumbersome.  Excel has several shortcuts for various routine calculations.  A simpler way to find the add up or find the sum of all the numbers it to use the excel function “=sum.”

To calculate the sum identify the range of cells to be included; for the data set below type, the equal sign and sum. A left parenthesis, will appear for you to type the reference numbers for the first and last cells to include. The formula is complete when you type the right parenthesis, write =sum(B5:B14).  You may also select a range of data by clicking on the first cell and dragging all the way to the end of the range. The value of the sum appears in the cell when you hit enter.  Write “total” in the cell next to help you keep track of the calculation results.

In the beginning of the learning activity, we talked about finding the average of a data set.  The average is defined as the sum of all the data points divided by the number of data points. Excel uses “/” to indicate division. The figure below shows and explains how several formulas can be used to determine average for data in a range. Try out each of the three formulas and see if you get the same answer in each case. If not there is something wrong with your syntax.

The Excel formula for the average calculation can be “=sum(B5:B14)/10” or “=sum(B5:B14)/A14”. Both equations will give you the same answer. Cell A14 has the total number of data points included in the data set.  Excel also as a shortcut formula for calculating average, “=AVERAGE”. The number of data points is apparent to Excel from the number of cells in the range and will automatically divide by the number of data points.