Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Spreadsheet Formulas
    [Collapse]Functions
      Mathematical Functions
      Statistical Functions
      Date and Time Functions
      Text Functions
      Financial Functions
      Logical Functions
      Lookup and Reference Functions
      Engineering Functions
      Information Functions
      Compatibility Functions
      Database Functions
      Web Functions
      User-Defined Functions (UDF)
      Real Time Data (RTD) function
     Operators
     Array Formulas
     Calculation
     Formula Engine
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Mathematical Functions

This document briefly describes mathematical functions implemented in the XtraSpreadsheet.

Name

Description

Syntax

ABSReturns the absolute value of a number. The absolute value of a number is the number without its sign.ABS(number)
ACOSReturns the arccosine, or inverse cosine, of a number. The returned angle is given in radians in the range of -pi/2 to pi/2.ACOS(number)
The argument must be in the range of -1 to 1.
ACOSHReturns the inverse hyperbolic cosine of a number.ACOSH(number)
ACOTReturns the inverse cotangent (the arccotangent) of a number.ACOT(number)
ACOTHReturns the inverse hyperbolic cotangent of the number.ACOTH(number)
ARABICConverts a Roman numeral to an Arabic numeral.ARABIC(text)
ASINReturns the arcsine, or inverse sine, of a number. The returned angle is given in radians in the range of -pi/2 to pi/2.ASIN(number)
The argument must be in the range of -1 to 1.
ASINHReturns the inverse hyperbolic sine of a numberASINH(number)
ATANReturns the arctangent, or inverse tangent, of a number. The returned angle is given in radians in the range -pi/2 to pi/2.ATAN(number)
ATAN2Calculates the arctangent (i.e. the inverse tangent) of a pair of x and y coordinates, and returns an angle, in radians.ATAN2( x_num, y_num )
ATANHReturns the inverse hyperbolic tangent of a given number.ATANH(number)
BASEConverts a number into a text representation with the given base.BASE(number, radix, min_length)
The number must be a positive integer less than 2^53. The radix is the base into what the number is converted. The radix is an integer >= 2 and <= 36. Min_length is optional, it is the minimum length of the returned string. If the Min_length parameter is present, leading zeros are added if required.
CEILINGReturns a number rounded up, away from zero, to the nearest multiple of significance. CEILING(number, significance)
The number is the value to round, the significance is the multiple to which you want to round. If the number is negative, and the significance is negative, the value is rounded down, away from zero. If the number is negative, and the significance is positive, the value is rounded up towards zero.
CEILING.MATHRounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.CEILING.MATH(number, significance, mode)
The number is the value to round, the optional significance parameter is the multiple to which you want to round. The optional mode parameter affects negative numbers only and specifies whether the number is rounded toward zero (mode = 0) or away from zero (mode <> 0).
CEILING.PRECISEReturns number rounded up to the nearest integer or to the nearest multiple of significance.CEILING.PRECISE(number, significance )
.
COMBINReturns the number of combinations for a given number of items.COMBIN( n, k )
n is the number of items in the set, k is the number of items to choose from the set.
COMBINAReturns the number of combinations (with repetitions) for a given number of items.COMBINA( n, k )
n is the number of items in the set, k is the number of items to choose from the set.
COSReturns the cosine of the given angle.COS(number)
The number is the angle in radians.
COSHReturns the hyperbolic cosine of a number.COSH(number)
The number is any real number.
COTReturns the cotangent of an angle.COT(number)
COTHReturns the hyperbolic cotangent of a number.COTH(number)
CSCReturns the cosecant of an angle.CSC(number)
CSCHReturns the hyperbolic cosecant of an angle.CSCH(number)
DECIMALConverts a text representation of a number in a given base into a decimal number.DECIMAL(text, radix)
DEGREESConverts radians into degrees.DEGREES(angle)
The angle is in radians.
EVENReturns a number rounded up to the nearest even integer.EVEN(number)
Regardless of the sign of the number, the value is rounded up when adjusted away from zero. If the number is an even integer, no rounding occurs.
EXPReturns the value of the mathematical constant e raised to the power of the number.EXP(number)
FACTReturns the factorial of a number.FACT(number)
The number is nonnegative. It the number is not an integer, it is truncated.
FACTDOUBLEReturns the double factorial of a number.FACTDOUBLE(number)
FLOORRounds the number down toward zero, to the nearest multiple of significance.FLOOR(number, significance)
The number is the value to round, the significance is the multiple to which you want to round.
FLOOR.MATHRounds a number down, to the nearest integer or to the nearest multiple of significance.FLOOR.MATH(number, significance, mode)
The number is the value to round, the optional significance is the multiple to which you want to round, the optional mode specifies the direction to round negative numbers (toward 0 if equal to 0, away from 0 if the mode is not zero).
FLOOR.PRECISERounds a numer down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.FLOOR.PRECISE(number, significance )
GCDReturns the greatest common divisor.GCD(number1, [number2], ...)
INTRounds a number down to the nearest integer.INT(number)
ISO.CEILINGReturns a number that is rounded up to the nearest integer or to the nearest multiple of significance.ISO.CEILING(number, significance)
The number is the value to round, the optional significance is the multiple to which you want to round.
LNReturns the natural logarithm of a number.LN(number)
The number is a positive real number.
LCMReturns the least common multiple.LCM(number1, [number2], ...)
LOGReturns the logarithm of a number to the base that you specify.LOG(number, base)
The number is a positive real number (the base is optional). It is the base of the logarithm. If omitted, the base is assumed to be 10.
LOG10Returns the base-10 logarithm of a number.LOG10(number)
The number is a positive real number.
MDETERMReturns the matrix determinant of an array.MDETERM(array)
MINVERSEReturns the matrix inverse of an array.MINVERSE(array)
MMULTReturns 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.MMULT(array1, array2)
The number of columns in array1 must be the same as the number of rows in array2, and both arrays must only contain numbers.
MODReturns the remainder after a number is divided by a divisor.MOD(number, divisor)
The number is the number for which to find the remainder, and the divisor is the number by which you want to divide the number. The result has the same sign as the divisor.
MROUNDReturns a number rounded to the desired multiple.MROUND(number, multiple)
The number is the value to round, the multiple is the value to which you want to round the number. The function rounds up, away from zero, if the remainder of dividing the number by multiple is greater than or equal to half the value of the multiple. For example, MROUND(10,3) returns 9 because the remainder of 10 divided by 3 is 1 which is less than half of 3. MROUND(11,3) returns 12.
MUNITReturns the unit matrix or the specified dimension.MUNIT(dimension)
MULTINOMIALReturns the multinomial of a set of numbers.MULTINOMIAL(number1, [number2], ...)
ODDRounds a number up to the nearest odd integer.ODD(number)
PIReturns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.PI()
POWERReturns the result of a number raised to a power.POWER(number, power)
The number is a real number, and the power is the exponent to which the number is raised. You can use the "^" operator instead.
PRODUCTMultiplies all numbers given as arguments and returns the product. PRODUCT(number1, number2, ...)
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.
QUOTIENTReturns the integer portion of a division.QUOTIENT(number, divisor)
RADIANSConverts degrees to radians.RADIANS(angle)
RANDReturns 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.RAND()
RANDBETWEENReturns a random integer number between the numbers that are specified. A new random integer number is returned every time the worksheet is calculated.RANDBETWEEN(bottom, top)
The bottom is the smallest integer, the top is the largest integer that the function will return.
ROMANConverts an arabic numeral to roman, as text.ROMAN(number, [form])
ROUNDRounds a number to a specified number of digits.ROUND(number, num_digits)
If num_digits is 0, the number is rounded to the nearest integer. If num_digits is greater than 0 (zero), then the number is rounded to the specified number of decimal places. If num_digits is less than 0, then the number is rounded to the left of the decimal point.
ROUNDDOWNRounds a number down, toward zero, to a specified number of digits.ROUNDDOWN(number, num_digits)
If num_digits is 0, the number is rounded to the nearest integer. If num_digits is greater than 0 (zero), then the number is rounded to the specified number of decimal places. If num_digits is less than 0, then the number is rounded to the left of the decimal point.
ROUNDUPRounds a number down, toward zero, to a specified number of digits.ROUNDUP(number, num_digits)
If num_digits is 0, the number is rounded to the nearest integer. If num_digits is greater than 0 (zero), then the number is rounded to the specified number of decimal places. If num_digits is less than 0, then the number is rounded to the left of the decimal point.
SECReturns the secant of an angle.SEC(number)
SECHReturns the hyperbolic secant of an angle.SECH(number)
SERIESSUMReturns the sum of a power series based on the formula.SERIESSUM(x, n, m, coefficients)
SIGNDetermines 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.SIGN(number)
SINReturns the sine of the given angle.SIN(number)
The number is the angle in radians.
SINHReturns the hyperbolic sine of a number.SIN()
number
SQRTReturns a positive square root.SQRT(number)
The number is any positive number.
SQRTPIMultiplies a specified number by pi and returns a square root of the product.SQRTPI(number)
The number is any positive number.
SUBTOTALPerforms a specified calculation (the sum, product, average, etc.) for a supplied set of valuesSUBTOTAL(function_num, ref1, ref2, ...)
Refer to the SUBTOTAL specifics endnote.

The function_num argument is a number that specifies the calculation type. Possible values for the function_num argument are listed in the following table. Note that by selecting a proper function_num argument you can ignore or include hidden values in calculation.

function_num (include hidden values)

function_num (ignore 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
SUMAdds all numbers that you specify as argumentsSUM(number1, number2, ...)
Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. 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.
SUMIFSums the values in a range that meet criteria that you specifySUMIF(range, criteria, sum_range)
Range is the range of cells that you wish to filter by criteria. Criteria is a number, expression, cell reference, text, or function that defines which cells will be added. Optional sum_range specifies the actual cells to add. If omitted, the cells specified in the Range argument are added.
SUMIFSSums the cells in a range that meet multiple criteria.SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Sum_range is the range of cells to sum. Criteria_range specifies the range in which to evaluate the specified criteria. The criteria is a number, expression, cell reference or text that defines which cells in the criteria_range argument will be added. You can specify several criteria ranges with specific criteria for each.
SUMPRODUCTReturns the sum of the products of the corresponding values in two or more supplied arrays.SUMPRODUCT(array1, array2, array3, ...)
The array arguments must have the same dimensions. Array entries that are not numeric are treated as if they were zeros.
SUMSQReturns the sum of the squares of the arguments.SUMSQ(number1, number2, ...)
You can also use a single array or a reference to an array instead of arguments separated by commas. Empty cells, logical values, text, or error values in the array or reference are ignored.
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays.SUMX2MY2(array_x, array_y)
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays.SUMX2PY2(array_x, array_y)
SUMXMY2Returns the sum of squares of differences of corresponding values in two arraysSUMXMY2(array_x, array_y)
Array_x and array_y should have the same number of values. If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
TANReturns the tangent of the given angle.TAN(angle)
The angle is measured in radians.
TANHReturns the hyperbolic tangent of a number.TANH(number)
The number is any real number.
TRUNCTruncates a number to a specified number of decimal places.TRUNC(number, num_digits)
If an optional parameter num_digits is omitted or has a value of 0, rounding is performed on an integer.

How would you rate this topic?​​​​​​​