Spreadsheet Math Fundamentals

Created by Janis L. Stevens, Lecturer, Dept. of Computer Technology, IUPUI, 2003

Description

Certain protocols exist when writing math formulas and functions in spreadsheets.

Write all formulas using cell addresses and not raw numbers. Results update automatically when raw numbers stored in separate input cells change. Otherwise, you must rewrite the formulas so Excel can calculate them, a time-consuming, error-prone process.

 

Definition of Terms, Math Order of Operations

 


 

Term

Description[1]

Formulas

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=) and uses cell addresses; for example, =A2+A4, adds the contents of Cells A2 and A4.

Functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the SUM function sums up a range of cells; for example, =sum(B10:B100), adds all the numbers in the range of B10 through B100.

Math Order of Operations If you combine several operators in a single formula, Excel performs the operations in the hierarchy shown below. If a formula contains operators with the same precedence  for example, if a formula contains both a multiplication and division operator  Excel evaluates the operators from left to right.

A helpful way to remember the math order of operations is to memorize the phrase displayed in Column 1. Parentheses are at the highest level. Addition and subtraction are at the lowest level. Read Column 1 as if it were a full sentence.

Please (Parentheses ( ) )

Excuse (Exponent ^)

My (Multiplication *)

Dear (Division /)

Aunt (Addition +)

Sally (Subtraction -)

 

Back to Top

 



[1] Descriptions are from Excel XP Help