LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 - Math and trigonometry functions

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

   Math and trigonometry functions

   ABS function

   ACOS function

   ACOSH function

   ASIN function

   ASINH function

   ATAN function

   ATANH function

   ATAN2 function

   CEILING function

   COMBIN function

   COS function

   COSH function

   DEGREES function

   EVEN function

   EXP function

   FACT function

   FACTDOUBLE function

   FLOOR function

   GCD function

   INT function

   LCM function

   LN function

   LOG function

   LOG10 function

   MDETERM function

   MINVERSE function

   MMULT function

   MOD function

   MROUND function

   MULTINOMIAL function

   ODD function

   PI function

   POWER function

   PRODUCT function

   QUOTIENT function

   RADIANS function

   RAND function

   RANDBETWEEN function

   ROMAN function

   ROUND function

   ROUNDDOWN function

   ROUNDUP function

   SERIESSUM function

   SIGN function

   SIN function

   SINH function

   SQRT function

   SQRTPI function

   SUBTOTAL function

   SUM function

   SUMIF function

   SUMIFS function

   SUMPRODUCT function

   SUMSQ function

   SUMXMY2 function

   SUMX2MY2 function

   SUMX2PY2 function

   TAN function

   TANH function

   TRUNC function

 

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 Fixed-Income Securities. 6th ed. New York, NY: Mcgraw-Hill Trade, 2000.

Hewlett-Packard, HP-12C Solutions Handbook. Palo Alto, CA: Hewlett-Packard, 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/McGraw-Hill, 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, Break-Evens, & 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 y-coordinates

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 base-10 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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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 y-coordinates. The arctangent is the angle from the x-axis 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 x-coordinate of the point.

Y_num   is the y-coordinate of the point.

Remarks

  • A positive result represents a counterclockwise angle from the x-axis; 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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

A

B

Formula

Description (Result)

=COMBIN(8,2)

Possible two-person 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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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:

  • If number is odd:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

A

 

Data

19.5

Formula

Description (Result)

=INT(8.9)

Rounds 8.9 down (8)

=INT(-8.9)

Rounds -8.9 down (-9)

=A2-INT(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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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 base-10 logarithm of a number.

Syntax

LOG10(number)

Number   is the positive real number for which you want the base-10 logarithm.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

A

B

Formula

Description (Result)

=LOG10(86)

Base-10 logarithm of 86 (1.934498451)

=LOG10(10)

Base-10 logarithm of 10 (1)

=LOG10(1E5)

Base-10 logarithm of 1E5 (5)

=LOG10(10^5)

Base-10 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 three-row, three-column array, A1:C3, the determinant is defined as:

MDETERM(A1:C3)

equals

A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*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 1E-16.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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 two-row, two-column 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*d-b*c)

b/(b*c-a*d)

Row 2

c/(b*c-a*d)

a/(a*d-b*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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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()*(b-a)+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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

A

B

Formula

Description (Result)

=SIGN(10)

Sign of a positive number (1)

=SIGN(4-4)

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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 3-D 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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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 non-numeric 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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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

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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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.):

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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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

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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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

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 12-hour 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?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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

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 12-hour 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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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)


                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us