Using spreadsheets to calculate figures is much more efficient than calculating manually or using a calculator. Spreadsheet formulas can contain functions, references, operators and constants. Functions are prewritten calculations included with your spreadsheet software. One of the most common functions is the sum function (sum()). References are cell addresses (A2, B2, B3, etc.). Operators identify the calculation you want to perform (+,-, /, etc.). Constants are numbers or text values (1, 2, 3, etc.).
Enter a Calculation into a Spreadsheet
Open Microsoft Excel. Enter column headers in row one. Column headers describe the data in the cells below it. Row numbers run down the left side of the spreadsheet. Column letters run across the top of the spreadsheet.
For an example of how a calculation is done in a spreadsheet, we will calculate the average weekly wages of an employee over a four-week period.
Row 1: A1="Employee ID," B1="Week 1," C1="Week 2," D1="Week 3," E1="Week 4" and F1="Avg. Pay for Week." These are the column headers.
Row 2: B1=BC5552, B2=100, C2=200, D2=300, E2=400. These numbers are the constants. The addresses of the cells these constants reside in are called cell references.
Enter your formula to calculate the variables. All formulas begin with an equal (=) sign. Use functions, operators, cell references or constants or any combination of these items. For more complicated spreadsheets and calculations, cell references are preferred. Cell references allow you to spot inconsistencies more quickly because in spreadsheets, the result of the formula, not the formula, is what is visible at a glance in each cell.
All of the following formulas calculate the average of cells B2, C2, D2 and E2. The average is 250.
Example using operators only:
In this example, + (plus) and / (divided by) are the operators.
Example using a combination of an operator and function:
In this example, /(divided by) is an operator and "SUM" is a function.
Example using a function only:
In this example, "AVERAGE" is the function. The colon means include all cells from B2 to E2. This formula is calculating all cells included in that range (B2, C2, D2 and E2).
Examples using constants:
=AVERAGE(100, 200, 300,400)
Access Spreadsheet Functions Libraries
Open Excel and select the cell in which you want to insert the function. Go to the "Formulas" tab, click the "Insert Function" button in the "Function Library." Select your function in the "Insert Function" dialog box and click the "OK" button to return to the spreadsheet. Use this method to insert a function in Microsoft Excel.
Open OpenOffice Calc and click the cell where you want to place the function. Press Ctrl+F2 to open the function wizard. Select the function, click next. Enter your constants or cell references. Click the "OK" button to return to the spreadsheet.
Open Google Docs Spreadsheet and click "Insert" in the top menu, select "Function" and "More." Select your function from the "Insert a function" dialog box and click the "OK" button to return to the spreadsheet.
Tips & Warnings
- Math parenthesis rules apply in spreadsheets. Inner-most parenthesis are calculated first.
- Photo Credit Push/Photodisc/Getty Images
How to Calculate Bollinger Bands in Excel
Bollinger bands are a mathematical tool used to predict the prices of securities based on their past performance. There are three bands:...