
This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

A bibliography of financial, statistical, and engineering methods
The following books provide detailed information on financial, statistical, and engineering methods.
Abramowitz, Milton, and Irene A. Stegun. Handbook of Mathematical Functions with Formulas, Graphs, and Mathematical Tables. 10th ed. New York, NY: Dover Publications, 1974.
Antkies, Candace Mary. Standard Securities Calculation Methods: Fixed Income Securities Formulas for Price, Yield and Accrued Interest: Volume 1. New York, NY: Securities Industry Association.
Antkies, Candace Mary. Standard Securities Calculation Methods, Fixed Income Securities Formulas for Analytic Measures: Volume 2. New York, NY: Securities Industry Association.
Devore, Jay L. Probability and Statistics for Engineering and the Sciences. 5th ed. Pacific Grove, CA: Duxbury Press, 1998.
Fabozzi, Frank J. The Handbook of FixedIncome Securities. 6th ed. New York, NY: McgrawHill Trade, 2000.
HewlettPackard, HP12C Solutions Handbook. Palo Alto, CA: HewlettPackard, 2001.
McCall, Robert B. Fundamental Statistics for the Behavioral Sciences. 7th ed. Pacific Grove, CA: Brooks Cole, 1998.
Monks, Joseph G. Operations Management: Theory and Problems. 3rd ed. New York, NY: Macmillan/McGrawHill, 1988.
Press, W. H., B. P. Flannery, S. A. Teukolsky, and W. T. Vetterling. Numerical Recipes in C: The Art of Scientific Computing. 2nd ed. New York, NY: Cambridge University Press, 1993.
Sokal, Robert R., and F. James Rohlf. Biometry: The Principles and Practice of Statistics in Biological Research. 3rd ed. New York, NY: W. H. Freeman, 1994.
Stigum, Marcia, and John Mann. Money Market Calculations: Yields, BreakEvens, & Arbitrage. 2nd ed. Homewood, IL: Irwin Professional, 1991.
See Also
Excel > Function reference > Math and trigonometry
Math and trigonometry functions
Click one of the links in the following list to see detailed help about the function.
Function

Description

ABS

Returns the absolute value of a number

ACOS

Returns the arccosine of a number

ACOSH

Returns the inverse hyperbolic cosine of a number

ASIN

Returns the arcsine of a number

ASINH

Returns the inverse hyperbolic sine of a number

ATAN

Returns the arctangent of a number

ATAN2

Returns the arctangent from x and ycoordinates

ATANH

Returns the inverse hyperbolic tangent of a number

CEILING

Rounds a number to the nearest integer or to the nearest multiple of significance

COMBIN

Returns the number of combinations for a given number of objects

COS

Returns the cosine of a number

COSH

Returns the hyperbolic cosine of a number

DEGREES

Converts radians to degrees

EVEN

Rounds a number up to the nearest even integer

EXP

Returns e raised to the power of a given number

FACT

Returns the factorial of a number

FACTDOUBLE

Returns the double factorial of a number

FLOOR

Rounds a number down, toward zero

GCD

Returns the greatest common divisor

INT

Rounds a number down to the nearest integer

LCM

Returns the least common multiple

LN

Returns the natural logarithm of a number

LOG

Returns the logarithm of a number to a specified base

LOG10

Returns the base10 logarithm of a number

MDETERM

Returns the matrix determinant of an array

MINVERSE

Returns the matrix inverse of an array

MMULT

Returns the matrix product of two arrays

MOD

Returns the remainder from division

MROUND

Returns a number rounded to the desired multiple

MULTINOMIAL

Returns the multinomial of a set of numbers

ODD

Rounds a number up to the nearest odd integer

PI

Returns the value of pi

POWER

Returns the result of a number raised to a power

PRODUCT

Multiplies its arguments

QUOTIENT

Returns the integer portion of a division

RADIANS

Converts degrees to radians

RAND

Returns a random number between 0 and 1

RANDBETWEEN

Returns a random number between the numbers you specify

ROMAN

Converts an arabic numeral to roman, as text

ROUND

Rounds a number to a specified number of digits

ROUNDDOWN

Rounds a number down, toward zero

ROUNDUP

Rounds a number up, away from zero

SERIESSUM

Returns the sum of a power series based on the formula

SIGN

Returns the sign of a number

SIN

Returns the sine of the given angle

SINH

Returns the hyperbolic sine of a number

SQRT

Returns a positive square root

SQRTPI

Returns the square root of (number * pi)

SUBTOTAL

Returns a subtotal in a list or database

SUM

Adds its arguments

SUMIF

Adds the cells specified by a given criteria

SUMIFS

Adds the cells in a range that meet multiple criteria

SUMPRODUCT

Returns the sum of the products of corresponding array components

SUMSQ

Returns the sum of the squares of the arguments

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays

SUMXMY2

Returns the sum of squares of differences of corresponding values in two arrays

TAN

Returns the tangent of a number

TANH

Returns the hyperbolic tangent of a number

TRUNC

Truncates a number to an integer

See Also
Excel > Function reference > Math and trigonometry
ABS function
Excel 2007
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Syntax
ABS(number)
Number is the real number of which you want the absolute value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

4

Formula

Description (Result)

=ABS(2)

Absolute value of 2 (2)

=ABS(2)

Absolute value of 2 (2)

=ABS(A2)

Absolute value of 4 (4)


See Also
Excel > Function reference > Math and trigonometry
ACOS function
Excel 2007
Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi.
Syntax
ACOS(number)
Number is the cosine of the angle you want and must be from 1 to 1.
Remark
If you want to convert the result from radians to degrees, multiply it by 180/PI() or use the DEGREES function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ACOS(0.5)

Arccosine of 0.5 in radians, 2*pi/3 (2.094395)

=ACOS(0.5)*180/PI()

Arccosine of 0.5 in degrees (120)

=DEGREES(ACOS(0.5))

Arccosine of 0.5 in degrees (120)


See Also
Excel > Function reference > Math and trigonometry
ACOSH function
Excel 2007
Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number.
Syntax
ACOSH(number)
Number is any real number equal to or greater than 1.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ACOSH(1)

Inverse hyperbolic cosine of 1 (0)

=ACOSH(10)

Inverse hyperbolic cosine of 10 (2.993223)


See Also
Excel > Function reference > Math and trigonometry
ASIN function
Excel 2007
Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range pi/2 to pi/2.
Syntax
ASIN(number)
Number is the sine of the angle you want and must be from 1 to 1.
Remark
To express the arcsine in degrees, multiply the result by 180/PI( ) or use the DEGREES function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ASIN(0.5)

Arcsine of 0.5 in radians, pi/6 (0.5236)

=ASIN(0.5)*180/PI()

Arcsine of 0.5 in degrees (30)

=DEGREES(ASIN(0.5))

Arcsine of 0.5 in degrees (30)


See Also
Excel > Function reference > Math and trigonometry
ASINH function
Excel 2007
Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number, so ASINH(SINH(number)) equals number.
Syntax
ASINH(number)
Number is any real number.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ASINH(2.5)

Inverse hyperbolic sine of 2.5 (1.64723)

=ASINH(10)

Inverse hyperbolic sine of 10 (2.998223)


See Also
Excel > Function reference > Math and trigonometry
ATAN function
Excel 2007
Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range pi/2 to pi/2.
Syntax
ATAN (number)
Number is the tangent of the angle you want.
Remark
To express the arctangent in degrees, multiply the result by 180/PI( ) or use the DEGREES function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ATAN(1)

Arctangent of 1 in radians, pi/4 (0.785398)

=ATAN(1)*180/PI()

Arctangent of 1 in degrees (45)

=DEGREES(ATAN(1))

Arctangent of 1 in degrees (45)


See Also
Excel > Function reference > Math and trigonometry
ATANH function
Excel 2007
Returns the inverse hyperbolic tangent of a number. Number must be between 1 and 1 (excluding 1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number, so ATANH(TANH(number)) equals number.
Syntax
ATANH(number)
Number is any real number between 1 and 1.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ATANH(0.76159416)

Inverse hyperbolic tangent of 0.76159416 (1, approximately)

=ATANH(0.1)

Inverse hyperbolic tangent of 0.1 (0.10034)


See Also
Excel > Function reference > Math and trigonometry
ATAN2 function
Excel 2007
Returns the arctangent, or inverse tangent, of the specified x and ycoordinates. The arctangent is the angle from the xaxis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between pi and pi, excluding pi.
Syntax
ATAN2(x_num,y_num)
X_num is the xcoordinate of the point.
Y_num is the ycoordinate of the point.
Remarks
 A positive result represents a counterclockwise angle from the xaxis; a negative result represents a clockwise angle.
 ATAN2(a,b) equals ATAN(b/a), except that a can equal 0 in ATAN2.
 If both x_num and y_num are 0, ATAN2 returns the #DIV/0! error value.
 To express the arctangent in degrees, multiply the result by 180/PI( ) or use the DEGREES function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ATAN2(1, 1)

Arctangent of the point 1,1 in radians, pi/4 (0.785398)

=ATAN2(1, 1)

Arctangent of the point 1,1 in radians, 3*pi/4 (2.35619)

=ATAN2(1, 1)*180/PI()

Arctangent of the point 1,1 in degrees (135)

=DEGREES(ATAN2(1, 1))

Arctangent of the point 1,1 in degrees (135)


See Also
Excel > Function reference > Math and trigonometry
CEILING function
Excel 2007
Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.
Syntax
CEILING(number,significance)
Number is the value you want to round.
Significance is the multiple to which you want to round.
Remarks
 If either argument is nonnumeric, CEILING returns the #VALUE! error value.
 Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
 If number and significance have different signs, CEILING returns the #NUM! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=CEILING(2.5, 1)

Rounds 2.5 up to nearest multiple of 1 (3)

=CEILING(2.5, 2)

Rounds 2.5 up to nearest multiple of 2 (4)

=CEILING(2.5, 2)

Returns an error, because 2.5 and 2 have different signs (#NUM!)

=CEILING(1.5, 0.1)

Rounds 1.5 up to the nearest multiple of 0.1 (1.5)

=CEILING(0.234, 0.01)

Rounds 0.234 up to the nearest multiple of 0.01 (0.24)


See Also
Excel > Function reference > Math and trigonometry
COMBIN function
Excel 2007
Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items.
Syntax
COMBIN(number,number_chosen)
Number is the number of items.
Number_chosen is the number of items in each combination.
Remarks
 Numeric arguments are truncated to integers.
 If either argument is nonnumeric, COMBIN returns the #VALUE! error value.
 If number < 0, number_chosen < 0, or number < number_chosen, COMBIN returns the #NUM! error value.
 A combination is any set or subset of items, regardless of their internal order. Combinations are distinct from permutations, for which the internal order is significant.
 The number of combinations is as follows, where number = n and number_chosen = k:
where:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=COMBIN(8,2)

Possible twoperson teams that can be formed from 8 candidates (28)


See Also
Excel > Function reference > Math and trigonometry
COS function
Excel 2007
Returns the cosine of the given angle.
Syntax
COS(number)
Number is the angle in radians for which you want the cosine.
Remark
If the angle is in degrees, either multiply the angle by PI()/180 or use the RADIANS function to convert the angle to radians.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=COS(1.047)

Cosine of 1.047 radians (0.500171)

=COS(60*PI()/180)

Cosine of 60 degrees (0.5)

=COS(RADIANS(60))

Cosine of 60 degrees (0.5)


See Also
Excel > Function reference > Math and trigonometry
COSH function
Excel 2007
Returns the hyperbolic cosine of a number.
Syntax
COSH(number)
Number is any real number for which you want to find the hyperbolic cosine.
Remark
The formula for the hyperbolic cosine is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=COSH(4)

Hyperbolic cosine of 4 (27.30823)

=COSH(EXP(1))

Hyperbolic cosine of the base of the natural logarithm (7.610125)


See Also
Excel > Function reference > Math and trigonometry
DEGREES function
Excel 2007
Converts radians into degrees.
Syntax
DEGREES(angle)
Angle is the angle in radians that you want to convert.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=DEGREES(PI())

Degrees of pi radians (180)


See Also
Excel > Function reference > Math and trigonometry
EVEN function
Excel 2007
Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts rows of one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the crate's capacity.
Syntax
EVEN(number)
Number is the value to round.
Remarks
 If number is nonnumeric, EVEN returns the #VALUE! error value.
 Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an even integer, no rounding occurs.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=EVEN(1.5)

Rounds 1.5 up to the nearest even integer (2)

=EVEN(3)

Rounds 3 up to the nearest even integer (4)

=EVEN(2)

Rounds 2 up to the nearest even integer (2)

=EVEN(1)

Rounds 1 up to the nearest even integer (2)


See Also
Excel > Function reference > Math and trigonometry
EXP function
Excel 2007
Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.
Syntax
EXP(number)
Number is the exponent applied to the base e.
Remarks
 To calculate powers of other bases, use the exponentiation operator (^).
 EXP is the inverse of LN, the natural logarithm of number.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=EXP(1)

Approximate value of e (2.718282)

=EXP(2)

Base of the natural logarithm e raised to the power of 2 (7.389056)


See Also
Excel > Function reference > Math and trigonometry
FACT function
Excel 2007
Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
Syntax
FACT(number)
Number is the nonnegative number for which you want the factorial. If number is not an integer, it is truncated.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=FACT(5)

Factorial of 5, or 1*2*3*4*5 (120)

=FACT(1.9)

Factorial of the integer of 1.9 (1)

=FACT(0)

Factorial of 0 (1)

=FACT(1)

Negative numbers cause an error value (#NUM!)

=FACT(1)

Factorial of 1 (1)


See Also
Excel > Function reference > Math and trigonometry
FACTDOUBLE function
Returns the double factorial of a number.
Syntax
FACTDOUBLE(number)
Number is the value for which to return the double factorial. If number is not an integer, it is truncated.
Remarks
 If number is nonnumeric, FACTDOUBLE returns the #VALUE! error value.
 If number is negative, FACTDOUBLE returns the #NUM! error value.
 If number is even:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=FACTDOUBLE(6)

Double factorial of 6 (48)

=FACTDOUBLE(7)

Double factorial of 7 (105)


See Also
Excel > Function reference > Math and trigonometry
FLOOR function
Excel 2007
Rounds number down, toward zero, to the nearest multiple of significance.
Syntax
FLOOR(number,significance)
Number is the numeric value you want to round.
Significance is the multiple to which you want to round.
Remarks
 If either argument is nonnumeric, FLOOR returns the #VALUE! error value.
 If number and significance have different signs, FLOOR returns the #NUM! error value.
 Regardless of the sign of number, a value is rounded down when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=FLOOR(2.5, 1)

Rounds 2.5 down to nearest multiple of 1 (2)

=FLOOR(2.5, 2)

Rounds 2.5 down to nearest multiple of 2 (2)

=FLOOR(2.5, 2)

Returns an error, because 2.5 and 2 have different signs (#NUM!)

=FLOOR(1.5, 0.1)

Rounds 1.5 down to the nearest multiple of 0.1 (1.5)

=FLOOR(0.234, 0.01)

Rounds 0.234 down to the nearest multiple of 0.01 (0.23)


See Also
Excel > Function reference > Math and trigonometry
GCD function
Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.
Syntax
GCD(number1,number2, ...)
Number1, number2, ... are 1 to 255 values. If any value is not an integer, it is truncated.
Remarks
 If any argument is nonnumeric, GCD returns the #VALUE! error value.
 If any argument is less than zero, GCD returns the #NUM! error value.
 One divides any value evenly.
 A prime number has only itself and one as even divisors.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=GCD(5, 2)

Greatest common divisor of 5 and 2 (1)

=GCD(24, 36)

Greatest common divisor of 24 and 36 (12)

=GCD(7, 1)

Greatest common divisor of 7 and 1 (1)

=GCD(5, 0)

Greatest common divisor of 5 and 0 (5)


See Also
Excel > Function reference > Math and trigonometry
INT function
Excel 2007
Rounds a number down to the nearest integer.
Syntax
INT(number)
Number is the real number you want to round down to an integer.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

19.5

Formula

Description (Result)

=INT(8.9)

Rounds 8.9 down (8)

=INT(8.9)

Rounds 8.9 down (9)

=A2INT(A2)

Returns the decimal part of a positive real number in cell A2 (0.5)


See Also
Excel > Function reference > Math and trigonometry
LCM function
Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use LCM to add fractions with different denominators.
Syntax
LCM(number1,number2, ...)
Number1, number2,... are 1 to 255 values for which you want the least common multiple. If value is not an integer, it is truncated.
Remarks
 If any argument is nonnumeric, LCM returns the #VALUE! error value.
 If any argument is less than zero, LCM returns the #NUM! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=LCM(5, 2)

Least common multiple of 5 and 2 (10)

=LCM(24, 36)

Least common multiple of 24 and 36 (72)


See Also
Excel > Function reference > Math and trigonometry
LN function
Excel 2007
Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
Syntax
LN(number)
Number is the positive real number for which you want the natural logarithm.
Remark
LN is the inverse of the EXP function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=LN(86)

Natural logarithm of 86 (4.454347)

=LN(2.7182818)

Natural logarithm of the value of the constant e (1)

=LN(EXP(3))

Natural logarithm of e raised to the power of 3 (3)


See Also
Excel > Function reference > Math and trigonometry
LOG function
Excel 2007
Returns the logarithm of a number to the base you specify.
Syntax
LOG(number,base)
Number is the positive real number for which you want the logarithm.
Base is the base of the logarithm. If base is omitted, it is assumed to be 10.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=LOG(10)

Logarithm of 10 (1)

=LOG(8, 2)

Logarithm of 8 with base 2 (3)

=LOG(86, 2.7182818)

Logarithm of 86 with base e (4.454347)


See Also
Excel > Function reference > Math and trigonometry
LOG10 function
Excel 2007
Returns the base10 logarithm of a number.
Syntax
LOG10(number)
Number is the positive real number for which you want the base10 logarithm.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=LOG10(86)

Base10 logarithm of 86 (1.934498451)

=LOG10(10)

Base10 logarithm of 10 (1)

=LOG10(1E5)

Base10 logarithm of 1E5 (5)

=LOG10(10^5)

Base10 logarithm of 10^5 (5)


See Also
Excel > Function reference > Math and trigonometry
MDETERM function
Excel 2007
Returns the matrix determinant of an array.
Syntax
MDETERM(array)
Array is a numeric array with an equal number of rows and columns.
Remarks
 Array can be given as a cell range, for example, A1:C3; as an array constant, such as {1,2,3;4,5,6;7,8,9}; or as a name to either of these.
 MDETERM returns the #VALUE! error when:
 Any cells in array are empty or contain text.
 Array does not have an equal number of rows and columns.
 The matrix determinant is a number derived from the values in array. For a threerow, threecolumn array, A1:C3, the determinant is defined as:
MDETERM(A1:C3)
equals
A1*(B2*C3B3*C2) + A2*(B3*C1B1*C3) + A3*(B1*C2B2*C1)
 Matrix determinants are generally used for solving systems of mathematical equations that involve several variables.
 MDETERM is calculated with an accuracy of approximately 16 digits, which may lead to a small numeric error when the calculation is not complete. For example, the determinant of a singular matrix may differ from zero by 1E16.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

D

Data

Data

Data

Data

1

3

8

5

1

3

6

1

1

1

1

0

7

3

10

2

Formula

Description (Result)



=MDETERM(A2:D5)

Determinant of the matrix above (88)



=MDETERM({3,6,1;1,1,0;3,10,2})

Determinant of the matrix as an array constant (1)



=MDETERM({3,6;1,1})

Determinant of the matrix in the array constant (3)



=MDETERM({1,3,8,5;1,3,6,1})

Returns an error because the array does not have an equal number of rows and columns (#VALUE!)




See Also
Excel > Function reference > Math and trigonometry
MINVERSE function
Excel 2007
Returns the inverse matrix for the matrix stored in an array.
Syntax
MINVERSE(array)
Array is a numeric array with an equal number of rows and columns.
Remarks
 Array can be given as a cell range, such as A1:C3; as an array constant, such as {1,2,3;4,5,6;7,8,9}; or as a name for either of these.
 If any cells in array are empty or contain text, MINVERSE returns the #VALUE! error value.
 MINVERSE also returns the #VALUE! error value if array does not have an equal number of rows and columns.
 Formulas that return arrays must be entered as array formulas.
 Inverse matrices, like determinants, are generally used for solving systems of mathematical equations involving several variables. The product of a matrix and its inverse is the identity matrix — the square array in which the diagonal values equal 1, and all other values equal 0.
 As an example of how a tworow, twocolumn matrix is calculated, suppose that the range A1:B2 contains the letters a, b, c, and d that represent any four numbers. The following table shows the inverse of the matrix A1:B2.

Column A

Column B

Row 1

d/(a*db*c)

b/(b*ca*d)

Row 2

c/(b*ca*d)

a/(a*db*c)

 MINVERSE is calculated with an accuracy of approximately 16 digits, which may lead to a small numeric error when the cancellation is not complete.
 Some square matrices cannot be inverted and will return the #NUM! error value with MINVERSE. The determinant for a noninvertable matrix is 0.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Data

Data

4

1

2

0

Formula

Formula

=MINVERSE(A2:B3)





Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A5:B6 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 0.
Example 2
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

Data

Data

Data

1

2

1

3

4

1

0

2

0

Formula

Formula

Formula

=MINVERSE(A2:C4)










Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A6:C8 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 0.25.
Tip Use the INDEX function to access individual elements from the inverse matrix.
See Also
Excel > Function reference > Math and trigonometry
MMULT function
Excel 2007
Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.
Syntax
MMULT(array1,array2)
Array1, array2 are the arrays you want to multiply.
Remarks
 The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.
 Array1 and array2 can be given as cell ranges, array constants, or references.
 MMULT returns the #VALUE! error when:
 Any cells are empty or contain text.
 The number of columns in array1 is different from the number of rows in array2.
 The matrix product array a of two arrays b and c is:
where i is the row number, and j is the column number.
 Formulas that return arrays must be entered as array formulas.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Array 1

Array 1

1

3

7

2

Array 2

Array 2

2

0

0

2

Formula

Formula

=MMULT(A2:B3,A5:B6)





Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A8:B9 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.
Example 2
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Array 1

Array 1

3

0

2

0

Array 2

Array 2

2

0

0

2

Formula

Formula

=MMULT(A2:B3,A5:B6)





Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A8:B9 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 6.
See Also
Excel > Function reference > Math and trigonometry
MOD function
Excel 2007
Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Syntax
MOD(number,divisor)
Number is the number for which you want to find the remainder.
Divisor is the number by which you want to divide number.
Remarks
 If divisor is 0, MOD returns the #DIV/0! error value.
 The MOD function can be expressed in terms of the INT function:
MOD(n, d) = n  d*INT(n/d)
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=MOD(3, 2)

Remainder of 3/2 (1)

=MOD(3, 2)

Remainder of 3/2. The sign is the same as divisor (1)

=MOD(3, 2)

Remainder of 3/2. The sign is the same as divisor (1)

=MOD(3, 2)

Remainder of 3/2. The sign is the same as divisor (1)


See Also
Excel > Function reference > Math and trigonometry
MROUND function
Excel 2007
Returns a number rounded to the desired multiple.
Syntax
MROUND(number,multiple)
Number is the value to round.
Multiple is the multiple to which you want to round number.
Remark
MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=MROUND(10, 3)

Rounds 10 to a nearest multiple of 3 (9)

=MROUND(10, 3)

Rounds 10 to a nearest multiple of 3 (9)

=MROUND(1.3, 0.2)

Rounds 1.3 to a nearest multiple of 0.2 (1.4)

=MROUND(5, 2)

Returns an error, because 2 and 5 have different signs (#NUM!)


See Also
Excel > Function reference > Math and trigonometry
MULTINOMIAL function
Excel 2007
Returns the ratio of the factorial of a sum of values to the product of factorials.
Syntax
MULTINOMIAL(number1,number2, ...)
Number1,number2, ... are 1 to 255 values for which you want the multinomial.
Remarks
 If any argument is nonnumeric, MULTINOMIAL returns the #VALUE! error value.
 If any argument is less than zero, MULTINOMIAL returns the #NUM! error value.
 The multinomial is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=MULTINOMIAL(2, 3, 4)

Ratio of the factorial of a sum of 2, 3 and 4 to the product of factorials (1260)


See Also
Excel > Function reference > Math and trigonometry
ODD function
Excel 2007
Returns number rounded up to the nearest odd integer.
Syntax
ODD(number)
Number is the value to round.
Remarks
 If number is nonnumeric, ODD returns the #VALUE! error value.
 Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an odd integer, no rounding occurs.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ODD(1.5)

Rounds 1.5 up to the nearest odd integer (3)

=ODD(3)

Rounds 3 up to the nearest odd integer (3)

=ODD(2)

Rounds 2 up to the nearest odd integer (3)

=ODD(1)

Rounds 1 up to the nearest odd integer (1)

=ODD(2)

Rounds 2 up to the nearest odd integer (3)


See Also
Excel > Function reference > Math and trigonometry
PI function
Excel 2007
Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
Syntax
PI( )
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Radius

3

Formula

Description (Result)

=PI()

Pi (3.14159265358979)

=PI()/2

Pi/2 (1.570796327)

=PI()*(A2^2)

Area of a circle, with the radius above (28.27433388)


See Also
Excel > Function reference > Math and trigonometry
POWER function
Excel 2007
Returns the result of a number raised to a power.
Syntax
POWER(number,power)
Number is the base number. It can be any real number.
Power is the exponent to which the base number is raised.
Remark
The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=POWER(5,2)

5 squared (25)

=POWER(98.6,3.2)

98.6 raised to the power of 3.2 (2401077)

=POWER(4,5/4)

4 raised to the power of 5/4 (5.656854)


See Also
Excel > Function reference > Math and trigonometry
PRODUCT function
Excel 2007
This article describes the formula syntax and usage of the PRODUCT function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2.
The PRODUCT function is useful when you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.
Syntax
PRODUCT(number1, [number2], ...)
The PRODUCT function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
Note If an argument is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

Data



5



15



30



Formula

Description

Result

=PRODUCT(A2:A4)

Multiplies the numbers in cells A2 through A4.

2250

=PRODUCT(A2:A4, 2)

Multiplies the numbers in cells A2 through A4, and then multiplies that result by 2.

4500

=A2*A3*A4

Multiplies the numbers in cells A2 through A4 by using mathematical operators instead of the PRODUCT function.

2250


See Also
Excel > Function reference > Math and trigonometry
QUOTIENT function
Excel 2007
Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
Syntax
QUOTIENT(numerator,denominator)
Numerator is the dividend.
Denominator is the divisor.
Remark
If either argument is nonnumeric, QUOTIENT returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=QUOTIENT(5, 2)

Integer portion of 5/2 (2)

=QUOTIENT(4.5, 3.1)

Integer portion of 4.5/3.1 (1)

=QUOTIENT(10, 3)

Integer portion of 10/3 (3)


See Also
Excel > Function reference > Math and trigonometry
RADIANS function
Excel 2007
Converts degrees to radians.
Syntax
RADIANS(angle)
Angle is an angle in degrees that you want to convert.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=RADIANS(270)

270 degrees as radians (4.712389 or 3× /2 radians)


See Also
Excel > Function reference > Math and trigonometry
RAND function
Excel 2007
Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
Syntax
RAND( )
Remarks
 To generate a random real number between a and b, use:
RAND()*(ba)+a
 If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=RAND()

A random number between 0 and 1 (varies)

=RAND()*100

A random number greater than or equal to 0 but less than 100 (varies)


See Also
Excel > Function reference > Math and trigonometry
RANDBETWEEN function
Excel 2007
Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
Syntax
RANDBETWEEN(bottom,top)
Bottom is the smallest integer RANDBETWEEN will return.
Top is the largest integer RANDBETWEEN will return.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=RANDBETWEEN(1,100)

Random number between 1 and 100 (varies)

=RANDBETWEEN(1,1)

Random number between 1 and 1 (varies)


See Also
Excel > Function reference > Math and trigonometry
ROMAN function
Excel 2007
Converts an arabic numeral to roman, as text.
Syntax
ROMAN(number,form)
Number is the Arabic numeral you want converted.
Form is a number specifying the type of roman numeral you want. The roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases. See the example following
ROMAN(499,0)
below.
Form

Type

0 or omitted

Classic.

1

More concise. See example below.

2

More concise. See example below.

3

More concise. See example below.

4

Simplified.

TRUE

Classic.

FALSE

Simplified.

Remarks
 If number is negative, the #VALUE! error value is returned.
 If number is greater than 3999, the #VALUE! error value is returned.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ROMAN(499,0)

Classic roman numeral style for 499 (CDXCIX)

=ROMAN(499,1)

More concise version for 499 (LDVLIV)

=ROMAN(499,2)

More concise version for 499 (XDIX)

=ROMAN(499,3)

More concise version for 499 (VDIV)

=ROMAN(499,4)

More concise version for 499 (ID)

=ROMAN(2013,0)

Classic roman numeral style for 2013 (MMXIII)


See Also
Excel > Function reference > Math and trigonometry
ROUND function
Excel 2007
This article describes the formula syntax and usage of the ROUND function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula:
=ROUND(A1, 2)
The result of this function is 23.78.
Syntax
ROUND(number, num_digits)
The ROUND function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
 number Required. The number that you want to round.
 num_digits Required. The number of digits to which you want to round the number argument.
Remarks
 If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
 If num_digits is 0, the number is rounded to the nearest integer.
 If num_digits is less than 0, the number is rounded to the left of the decimal point.
 To always round up (away from zero), use the ROUNDUP function.
 To always round down (toward zero), use the ROUNDDOWN function.
 To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

Formula

Description

Result

=ROUND(2.15, 1)

Rounds 2.15 to one decimal place

2.2

=ROUND(2.149, 1)

Rounds 2.149 to one decimal place

2.1

=ROUND(1.475, 2)

Rounds 1.475 to two decimal places

1.48

=ROUND(21.5, 1)

Rounds 21.5 to one decimal place to the left of the decimal point

20


See Also
Excel > Function reference > Math and trigonometry
ROUNDDOWN function
Excel 2007
Rounds a number down, toward zero.
Syntax
ROUNDDOWN(number,num_digits)
Number is any real number that you want rounded down.
Num_digits is the number of digits to which you want to round number.
Remarks
 ROUNDDOWN behaves like ROUND, except that it always rounds a number down.
 If num_digits is greater than 0 (zero), then number is rounded down to the specified number of decimal places.
 If num_digits is 0, then number is rounded down to the nearest integer.
 If num_digits is less than 0, then number is rounded down to the left of the decimal point.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ROUNDDOWN(3.2, 0)

Rounds 3.2 down to zero decimal places (3)

=ROUNDDOWN(76.9,0)

Rounds 76.9 down to zero decimal places (76)

=ROUNDDOWN(3.14159, 3)

Rounds 3.14159 down to three decimal places (3.141)

=ROUNDDOWN(3.14159, 1)

Rounds 3.14159 down to one decimal place (3.1)

=ROUNDDOWN(31415.92654, 2)

Rounds 31415.92654 down to 2 decimal places to the left of the decimal (31400)


See Also
Excel > Function reference > Math and trigonometry
ROUNDUP function
Excel 2007
Rounds a number up, away from 0 (zero).
Syntax
ROUNDUP(number,num_digits)
Number is any real number that you want rounded up.
Num_digits is the number of digits to which you want to round number.
Remarks
 ROUNDUP behaves like ROUND, except that it always rounds a number up.
 If num_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.
 If num_digits is 0, then number is rounded up to the nearest integer.
 If num_digits is less than 0, then number is rounded up to the left of the decimal point.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ROUNDUP(3.2,0)

Rounds 3.2 up to zero decimal places (4)

=ROUNDUP(76.9,0)

Rounds 76.9 up to zero decimal places (77)

=ROUNDUP(3.14159, 3)

Rounds 3.14159 up to three decimal places (3.142)

=ROUNDUP(3.14159, 1)

Rounds 3.14159 up to one decimal place (3.2)

=ROUNDUP(31415.92654, 2)

Rounds 31415.92654 up to 2 decimal places to the left of the decimal (31500)


See Also
Excel > Function reference > Math and trigonometry
SERIESSUM function
Excel 2007
Returns the sum of a power series based on the formula:
Many functions can be approximated by a power series expansion.
Syntax
SERIESSUM(x,n,m,coefficients)
X is the input value to the power series.
N is the initial power to which you want to raise x.
M is the step by which to increase n for each term in the series.
Coefficients is a set of coefficients by which each successive power of x is multiplied. The number of values in coefficients determines the number of terms in the power series. For example, if there are three values in coefficients, then there will be three terms in the power series.
Remark
If any argument is nonnumeric, SERIESSUM returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Coefficients

=PI()/4

1

=1/FACT(2)

=1/FACT(4)

=1/FACT(6)

Formula

Description (Result)

=SERIESSUM(A2,0,2,A3:A6)

Approximation to the cosine of Pi/4 radians, or 45 degrees (0.707103)


See Also
Excel > Function reference > Math and trigonometry
SIGN function
Excel 2007
Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and 1 if the number is negative.
Syntax
SIGN(number)
Number is any real number.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=SIGN(10)

Sign of a positive number (1)

=SIGN(44)

Sign of zero (0)

=SIGN(0.00001)

Sign of a negative number (1)


See Also
Excel > Function reference > Math and trigonometry
SIN function
Excel 2007
Returns the sine of the given angle.
Syntax
SIN(number)
Number is the angle in radians for which you want the sine.
Remark
If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=SIN(PI())

Sine of pi radians (0, approximately)

=SIN(PI()/2)

Sine of pi/2 radians (1)

=SIN(30*PI()/180)

Sine of 30 degrees (0.5)

=SIN(RADIANS(30))

Sine of 30 degrees (0.5)


See Also
Excel > Function reference > Math and trigonometry
SINH function
Excel 2007
Returns the hyperbolic sine of a number.
Syntax
SINH(number)
Number is any real number.
Remark
The formula for the hyperbolic sine is:
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=SINH(1)

Hyperbolic sine of 1 (1.175201194)

=SINH(1)

Hyperbolic sine of 1 (1.175201194)


Example 2
You can use the hyperbolic sine function to approximate a cumulative probability distribution. Suppose a laboratory test value varies between 0 and 10 seconds. An empirical analysis of the collected history of experiments shows that the probability of obtaining a result, x, of less than t seconds is approximated by the following equation:
P(x<t) = 2.868 * SINH(0.0342 * t), where 0<t<10
To calculate the probability of obtaining a result of less than 1.03 seconds, substitute 1.03 for t.
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=2.868*SINH(0.0342*1.03)

Probability of obtaining a result of less than 1.03 seconds (0.101049063)


You can expect this result to occur about 101 times for every 1000 experiments.
See Also
Excel > Function reference > Math and trigonometry
SQRT function
Excel 2007
Returns a positive square root.
Syntax
SQRT(number)
Number is the number for which you want the square root.
Remark
If number is negative, SQRT returns the #NUM! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

16

Formula

Description (Result)

=SQRT(16)

Square root of 16 (4)

=SQRT(A2)

Square root of the number above. Because the number is negative, an error is returned (#NUM!)

=SQRT(ABS(A2))

Square root of the absolute value of the number above (4)


See Also
Excel > Function reference > Math and trigonometry
SQRTPI function
Excel 2007
Returns the square root of (number * pi).
Syntax
SQRTPI(number)
Number is the number by which pi is multiplied.
Remark
If number < 0, SQRTPI returns the #NUM! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=SQRTPI(1)

Square root of pi (1.772454)

=SQRTPI(2)

Square root of 2 * pi (2.506628)


See Also
Excel > Function reference > Math and trigonometry
SUBTOTAL function
Excel 2007
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Syntax
SUBTOTAL(function_num, ref1, ref2, ...)
Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.
Function_num
(includes hidden values)

Function_num
(ignores hidden values)

Function

1

101

AVERAGE

2

102

COUNT

3

103

COUNTA

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SUM

10

110

VAR

11

111

VARP

Ref1, ref2 are 1 to 254 ranges or references for which you want the subtotal.
Remarks
 If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
 For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
 The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
 The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
 If any of the references are 3D references, SUBTOTAL returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

120

10

150

23

Formula

Description (Result)

=SUBTOTAL(9,A2:A5)

Subtotal of the column above using the SUM function (303)

=SUBTOTAL(1,A2:A5)

Subtotal of the column above using the AVERAGE function (75.75)


See Also
Excel > Function reference > Math and trigonometry
SUM function
Excel 2007
Tags add; add cell; add column; formula; functions
What are tags?
This article describes the formula syntax and usage of the SUM function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
The SUM function adds all the numbers that you specify as arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.). Each argument can be a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), a constant (constant: A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.
Syntax
SUM(number1, [number2], [number3], [number4], ...)
The SUM function syntax has the following arguments:
 number1 Required. The first item that you want to add.
 number2, number3, number4, ... Optional. The remaining items that you want to add, up to a total of 255 items.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

11

12


A

B

C

Data



5



15



30



'5



TRUE



Formula

Description

Result

=SUM(3, 2)

Adds 3 and 2.

5

=SUM("5", 15, TRUE)

Adds 5, 15 and 1. The text value "5" is first translated into a number, and the logical value TRUE is first translated into the number 1.

21

=SUM(A2:A4)

Adds the values in cells A2 through A4.

40

=SUM(A2:A4, 15)

Adds the values in cells A2 through A4, and then adds 15 to that result.

55

=SUM(A5,A6, 2)

Adds the values in cells A5 and A6, and then adds 2 to that result. Because nonnumeric values in references are not translated — the value in cell A5 ('5) and the value in cell A6 (TRUE) are both treated as text — the values in those cells are ignored.

2


Remarks
 If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
 If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.
Related Office Online discussions
Read related questions and answers from other Microsoft Office customers.
See Also
Excel > Function reference > Math and trigonometry
SUMIF function
Excel 2007
This article describes the formula syntax and usage of the SUMIF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
You use the SUMIF function to sum the values in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula:
=SUMIF(B2:B25,">5")
In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
Note To sum cells based on multiple criteria, see SUMIFS function.
Syntax
SUMIF(range, criteria, [sum_range])
The SUMIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
 range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
 criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, 32, "32", "apples", or TODAY().
Important Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.
 sum_range Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
Notes
 The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using theupper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument. For example:
If range is

And sum_range is

Then the actual cells are

A1:A5

B1:B5

B1:B5

A1:A5

B1:B3

B1:B5

A1:B4

C1:D4

C1:D4

A1:B4

C1:C2

C1:D4

 You can use the wildcard characters — the question mark (?) and asterisk (*) — as the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

Property Value

Commission

Data

100,000

7,000

250,000

200,000

14,000


300,000

21,000


400,000

28,000


Formula

Description

Result

=SUMIF(A2:A5,">160000",B2:B5)

Sum of the commissions for property values over 160,000.

63,000

=SUMIF(A2:A5,">160000")

Sum of the property values over 160,000.

900,000

=SUMIF(A2:A5,300000,B2:B5)

Sum of the commissions for property values equal to 300,000.

21,000

=SUMIF(A2:A5,">" & C2,B2:B5)

Sum of the commissions for property values greater than the value in C2.

49,000


Example 2
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

11

12


A

B

C

Category

Food

Sales

Vegetables

Tomatoes

2300

Vegetables

Celery

5500

Fruits

Oranges

800


Butter

400

Vegetables

Carrots

4200

Fruits

Apples

1200

Formula

Description

Result

=SUMIF(A2:A7,"Fruits",C2:C7)

Sum of the sales of all foods in the "Fruits" category.

2000

=SUMIF(A2:A7,"Vegetables",C2:C7)

Sum of the sales of all foods in the "Vegetables" category.

12000

=SUMIF(B2:B7,"*es",C2:C7)

Sum of the sales of all foods that end in "es" (Tomatoes, Oranges, and Apples).

4300

=SUMIF(A2:A7,"",C2:C7)

Sum of the sales of all foods that do not have a category specified.

400


Related Office Online discussions
Read related questions and answers from other Microsoft Office customers.
See Also
Excel > Function reference > Math and trigonometry
SUMIFS function
Excel 2007
This article describes the formula syntax and usage of the SUMIFSfunction (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:
=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")
Important The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], …)
The SUMIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
 sum_range Required. One or more cells to sum, including numbers or names, ranges, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) that contain numbers. Blank and text values are ignored.
 criteria_range1 Required. The first range in which to evaluate the associated criteria.
 criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."
 criteria_range2, criteria2, … Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
Remarks
 Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two criteria_range arguments. If the first cell of criteria_range1 meets criteria1, and the first cell of criteria_range2 meets critera2, the first cell of sum_range is added to the sum, and so on, for the remaining cells in the specified ranges.
 Cells in the sum_range argument that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0 (zero).
 Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_range argument must contain the same number of rows and columns as the sum_range argument.
 You can use the wildcard characters — the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

11

12


A

B

C

Quantity Sold

Product

Salesperson

5

Apples

1

4

Apples

2

15

Artichokes

1

3

Artichokes

2

22

Bananas

1

12

Bananas

2

10

Carrots

1

33

Carrots

2

Formula

Description

Result

=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)

Adds the total number of products sold that begin with "A" and that were sold by Salesperson 1.

20

=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)

Adds the total number of products (not including Bananas) sold by Salesperson 1.

30


Example 2
Adding amounts from bank accounts based on interest paid
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

D

E

Totals

Account 1

Account 2

Account 3

Account 4

Amount in dollars

100

390

8321

500

Interest paid (2000)

1%

0.5%

3%

4%

Interest paid (2001)

1%

1.3%

2.1%

2%

Interest paid (2002)

0.5%

3%

1%

4%

Formula

Description

Result

=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%")

Total amounts from each bank account where the interest was greater than 3% for the year 2000 and greater than or equal to 2% for the year 2001.

500

=SUMIFS(B2:E2, B5:E5, ">=1%", B5:E5, "<=3%", B4:E4, ">1%")

Total amounts from each bank account where the interest was between 1% and 3% for the year 2002 and greater than 1% for the year 2001.

8711


Example 3
Adding rainfall for specific days
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

D

E

Daily Measurements

First Day

Second Day

Third Day

Fourth Day

Rain (total inches)

3.3

0.8

5.5

5.5

Average temperature (degrees)

55

39

39

57.5

Average wind speed (miles per hour)

6.5

19.5

6

6.5

Formula

Description

Result

=SUMIFS(B2:E2, B3:E3, ">=40", B4:E4, "<10")

Adds the total amount of rainfall for days when the average temperature was at least 40 degrees Fahrenheit and the average wind speed was less than 10 miles per hour.
Only cells B2 and E2 are summed because, for each column (B through E), the values in both rows 3 and 4 must meet criteria1 and criteria2, respectively. Cells B3 and B4 meet both criteria, as do E3 and E4. However, neither C3 nor C4 meet either criteria. Finally, although D4 meets criteria2, D3 fails to meet criteria1.

8.8


Example 4
Adding rainfall for morning and evening periods of specific days
This example expands on the data in Example 3, separating the rainfall, average temperatures, and average wind speed into two 12hour periods for each day.
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

D

E

Morning and Evening Measurements

First Day

Second Day

Third Day

Fourth Day

AM: rain (total inches)

1.3

0

1.5

3

PM: rain (total inches)

2

0.8

4

2.5

AM: average temperature (degrees)

56

44

40

38

PM: average temperature (degrees)

54

34

38

77

AM: average wind speed (miles per hour)

13

6

8

1

PM: average wind speed (miles per hour)

0

33

4

12

Formula

Description

Result

=SUMIFS(B2:E3, B4:E5, ">=40", B6:E7, "<10")

Adds the total amount of rainfall for 12hour periods when the average temperature was at least 40 degrees Fahrenheit and the average wind speed was less than 10 miles per hour.
Only cells B3, C2, and D2 are summed, because their corresponding cells meet both criteria. The corresponding cells for B3 are B5 and B7, the corresponding cells for C2 are C4 and C6, and the corresponding cells for D2 are D4 and D6.
As an example of data excluded from the operation, the corresponding cells of B2 (B4 and B6) do not meet both criteria; specifically, cell B6 fails because its value (13) is greater than criteria2 (10).

3.5


See Also
Excel > Function reference > Math and trigonometry
SUMPRODUCT function
Excel 2007
Multiplies corresponding components in the given arrays, and returns the sum of those products.
Syntax
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... are 2 to 255 arrays whose components you want to multiply and then add.
Remarks
 The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
 SUMPRODUCT treats array entries that are not numeric as if they were zeros.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

D

Array 1

Array 1

Array 2

Array 2

3

4

2

7

8

6

6

7

1

9

5

3

Formula

Description (Result)



=SUMPRODUCT(A2:B4, C2:D4)

Multiplies all the components of the two arrays and then adds the products — that is, 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. (156)




Remark
The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array. Using arrays provides a more general solution for doing operations similar to SUMPRODUCT. For example, you can calculate the sum of the squares of the elements in A2:B4 by using the formula =SUM(A2:B4^2) and pressing CTRL+SHIFT+ENTER.
See Also
Excel > Function reference > Math and trigonometry
SUMSQ function
Excel 2007
Returns the sum of the squares of the arguments.
Syntax
SUMSQ(number1,number2, ...)
Number1, number2, ... are 1 to 255 arguments for which you want the sum of the squares. You can also use a single array or a reference to an array instead of arguments separated by commas.
Remarks
 Arguments can either be numbers or names, arrays, or references that contain numbers.
 Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted.
 If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
 Arguments that are error values or text that cannot be translated into numbers cause errors.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=SUMSQ(3, 4)

Sum of the squares of 3 and 4 (25)


See Also
Excel > Function reference > Math and trigonometry
SUMXMY2 function
Excel 2007
Returns the sum of squares of differences of corresponding values in two arrays.
Syntax
SUMXMY2(array_x,array_y)
Array_x is the first array or range of values.
Array_y is the second array or range of values.
Remarks
 The arguments should be either numbers or names, arrays, or references that contain numbers.
 If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
 If array_x and array_y have a different number of values, SUMXMY2 returns the #N/A error value.
 The equation for the sum of squared differences is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

First array

Second array

2

6

3

5

9

11

1

7

8

5

7

4

5

4

Formula

Description (Result)

=SUMXMY2(A2:A8,B2:B8)

Sum of squares of differences of the two arrays above (79)

=SUMXMY2({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4})

Sum of squares of differences of the two arrays constants (79)


See Also
Excel > Function reference > Math and trigonometry
SUMX2MY2 function
Excel 2007
Returns the sum of the difference of squares of corresponding values in two arrays.
Syntax
SUMX2MY2(array_x,array_y)
Array_x is the first array or range of values.
Array_y is the second array or range of values.
Remarks
 The arguments should be either numbers or names, arrays, or references that contain numbers.
 If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
 If array_x and array_y have a different number of values, SUMX2MY2 returns the #N/A error value.
 The equation for the sum of the difference of squares is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

First array

Second array

2

6

3

5

9

11

1

7

8

5

7

4

5

4

Formula

Description (Result)

=SUMX2MY2(A2:A8,B2:B8)

Sum of the difference of squares of the two arrays above (55)

=SUMX2MY2({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4})

Sum of the difference of squares of the two arrays constants (55)


See Also
Excel > Function reference > Math and trigonometry
SUMX2PY2 function
Excel 2007
Returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations.
Syntax
SUMX2PY2(array_x,array_y)
Array_x is the first array or range of values.
Array_y is the second array or range of values.
Remarks
 The arguments should be either numbers or names, arrays, or references that contain numbers.
 If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
 If array_x and array_y have a different number of values, SUMX2PY2 returns the #N/A error value.
 The equation for the sum of the sum of squares is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

First array

Second array

2

6

3

5

9

11

1

7

8

5

7

4

5

4

Formula

Description (Result)

=SUMX2PY2(A2:A8,B2:B8)

Sum of the sum of squares of the two arrays above (521)

=SUMX2PY2({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4})

Sum of the sum of squares of the two arrays constants (521)


See Also
Excel > Function reference > Math and trigonometry
TAN function
Excel 2007
Returns the tangent of the given angle.
Syntax
TAN(number)
Number is the angle in radians for which you want the tangent.
Remark
If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=TAN(0.785)

Tangent of 0.785 radians (0.99920)

=TAN(45*PI()/180)

Tangent of 45 degrees (1)

=TAN(RADIANS(45))

Tangent of 45 degrees (1)


See Also
Excel > Function reference > Math and trigonometry
TANH function
Excel 2007
Returns the hyperbolic tangent of a number.
Syntax
TANH(number)
Number is any real number
Remark
The formula for the hyperbolic tangent is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=TANH(2)

Hyperbolic tangent of 2 (0.96403)

=TANH(0)

Hyperbolic tangent of 0 (0)

=TANH(0.5)

Hyperbolic tangent of 0.5 (0.462117)


See Also
Excel > Function reference > Math and trigonometry
TRUNC function
Excel 2007
Truncates a number to an integer by removing the fractional part of the number.
Syntax
TRUNC(number,num_digits)
Number is the number you want to truncate.
Num_digits is a number specifying the precision of the truncation. The default value for num_digits is 0 (zero).
Remark
TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative numbers:
TRUNC(4.3)
returns 4, but
INT(4.3)
returns 5 because 5 is the lower number.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=TRUNC(8.9)

Integer part of 8.9 (8)

=TRUNC(8.9)

Integer part of 8.9 (8)

=TRUNC(PI())

Integer part of pi (3)

