Contents of this Page:

Numeric Precision
Numeric Range
Error Numbers
Operators
Mathematical
Logical
Operator Precedence

Why Do I Need this Information?

The information provided on this page will help you as you consider the mathematical importance of the model you are creating.

Numeric Precision

Microsoft Excel wad developed to compete in the business software market where precision is probably not important for simple calculations.  However many of the real-world calculations used in computational science require many digits of precision to the right of the decimal point.

Excel's maintains an internal numeric precision of 15 digits.   By comparison a typical scientific calculator displays 10 digits but probably stores 12 digits while a CRAY 1 supercomputer has 15 digits in single-precision, floating point number.   Excel stores 15 digits internally, but rounds the value for the screen display according to the format of the cell.

Numeric Range

Numeric range determines the sensitivity of the spreadsheet to overflow and underflow errors.  Excel stores numbers between -1.798 x10 +308 and 1.798 x 10⁺³⁰⁸ for a numeric range of ±10⁺³⁰⁸.  A scientific calculator handles a range of 10^±99 and the CRAY 1 has a range of 10^±2500.  Although the largest number that Excel can store is 1.798 x 10⁺³⁰⁸,  the largest number that you can type is 9.999 x 10⁺³⁰⁷. If you type in a larger number, Excel will treat it as a character string.

Most computational science calculations have reasonable results somewhere in the range of 10^-40 to 10⁺⁴⁰.   When these numbers are used in an equation, the intermediate results are often quite large.  If the intermediate results exceed the range of the computer then an overflow condition will return an error.

Error Numbers

Excel treats seven error values as if they were numbers:
 
 

DIV/0!	Division by zero
#NAME	A variable name in a formula has not been defined
#N/A	No value is available
#NULL!	A result does not exist, or is an invalid intersection of two areas
#NUM!	Numeric overflow, underflow, or incorrect use of a number, such as SQRT(-1).
#REF!	Invalid cell  reference; the cell is not on the worksheet
#VALUE!	Invalid argument type, such as text where a number is required

Operators

Operators are the basic building blocks of mathematical formulas.  They determine how numbers are combined to produce results.  Excel has three types of operators: mathematical, logical and text.   The single text operator is concatenation (&), which joins two text strings together into one string of text.

Mathematical Operators

The mathematical operators are the standard set that you find in any higher level computer language or scientific calculator.

The two unary operators are percent (%) and negation (-).  The percent operator divides the number to its left by  100.  If the number with the % is the only number in the cell, then the cell's format is changed to percent.  Excel does not provide any positive unary operator.  A positive value is assumed if a unary operator is not present.

The mathematical operators are addition (+), subtraction (-) , multiplication (*), division (/), and exponentiation  (^).

Logical Operators

The logical or comparison operators are used to compare two numerical values or strings.  The set of logical operators in Excel includes equal to (=), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=) and not equal to (<>).  The results of these operations are the value TRUE or the value FALSE.  When used in mathematical formulas, TRUE has a value of 1 and FALSE has a value of 0.

Operator Precedence

The precedence of operators determines the order in which a formula is evaluated.  In any calculation, the operators listed in the following table with a precedence of 1 are executed first, then the operators with a precedence of 2 and so on.  If there are two operations of equal precedence in a formula, then they are evaluated from right to left.

For example in the formula A1=1+2*3,  2 will be multiplied by 3 first since the precedence for multiplication is 4 compared to 5 which is the precedence for addition.   After the 2*3 have been evaluated the result, 6, is then added to 1.  A1 would be set to 7.
 
 

Operator	Description	Precedence in Calculations
	Unary Operators
-	Negation (operates on value to its right)	1
%	Percent (operates on value to its left)	2
	Mathematical Operators
^	Exponentiation	3
*	Multiplication	4
/	Division	4
+	Addition	5
-	Subtraction	5
	Text Operator
&	Concatenation	6
	Logical Operators
=	Equal to	7
<	Less than	7
>	Greater than	7
<=	Less than or equal to	7
>=	Greater than or equal to	7
<>	Not equal to	7

 

Parentheses always override the operator precedence so they can be used to ensure that a formula is evaluated in the correct order.

If we modify our equation, A1=1+2+3 to A1=(1+2)*3, the operation inside the parentheses will be evaluated first.   The result, 3, will then be multiplied by 3 to give a final value for A1 of 9.