Math and trig functions
Math functions add, round, count and transform numbers. Most spreadsheets lean on a handful of them every day, starting with SUM and ROUND.
80 functions in this category.
Start here
- SUM
Adds numbers together. The most-used function in Excel.
- SUMIF
Adds up values that match one condition, such as sales for one region.
- SUMIFS
Adds values subject to several conditions at once, e.g. sales for a product in one quarter.
- SUBTOTAL
Totals a filtered list so the result changes with the filter. It also skips other SUBTOTAL results, preventing double counting.
- ROUND
Standard rounding, with halves going away from zero. Use it to fix a displayed total to its real value, not just its formatting.
- SUMPRODUCT
Multiplies lists item by item and adds the results. Also a flexible way to count or sum with multiple conditions.
Tasks that use math and trig functions
All math and trig functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
ABS | Strips the minus sign from a number. Handy for measuring the size of a gap or variance regardless of direction. | Beginner | 2007 or earlier | No |
ACOS | Works backwards from a cosine to the angle that produced it. Wrap in DEGREES if you want degrees. | Expert | 2007 or earlier | No |
ACOSH | Reverses COSH. Mostly seen in engineering and statistics formulas. | Expert | 2007 or earlier | No |
ACOT | Returns the angle whose cotangent is the given number. | Expert | 2013 | No |
ACOTH | Reverses COTH. A niche function mainly for maths and engineering work. | Expert | 2013 | No |
AGGREGATE | A SUBTOTAL-style function that can also ignore error values, which makes it the go-to for totalling or ranking a column that contains #N/A or #DIV/0!. It also covers percentiles, quartiles and LARGE/SMALL in one place. | Intermediate | 2010 | No |
ARABIC | Converts a Roman numeral into a regular number. Useful when cleaning data such as chapter or copyright-year labels. | Expert | 2013 | No |
ASIN | Takes a sine value and returns the angle that has it. | Expert | 2007 or earlier | No |
ASINH | Reverses SINH. Rare outside technical models. | Expert | 2007 or earlier | No |
ATAN | Takes a tangent (a slope) and returns the angle it corresponds to. | Expert | 2007 or earlier | No |
ATAN2 | Gives the direction of a point from the origin, using both coordinates so it gets the quadrant right. Used in mapping, bearings and geometry. | Expert | 2007 or earlier | No |
ATANH | Reverses TANH. Appears in some statistical transformations. | Expert | 2007 or earlier | No |
BASE | Writes a decimal number in binary, hex, or any base up to 36. Useful for IDs, flags and checking bit patterns. | Expert | 2013 | No |
CEILING.MATH | Rounds up to a chosen multiple, such as the next 5 or the next 0.25. It handles negative numbers in a controlled way, unlike the older CEILING. | Intermediate | 2013 | No |
CEILING.PRECISE | Always rounds toward positive infinity, whatever the sign of the inputs. A simpler cousin of CEILING.MATH. | Intermediate | 2010 | No |
COMBIN | Counts combinations, for example how many 3-person teams can be formed from 8 people. Order does not matter. | Intermediate | 2007 or earlier | No |
COMBINA | Like COMBIN but allows repeats, such as counting the possible 2-scoop orders from 3 ice-cream flavours. | Expert | 2013 | No |
COS | Returns the cosine of an angle. Convert degrees first with RADIANS. | Expert | 2007 or earlier | No |
COSH | Computes the hyperbolic cosine, which appears in curves like hanging cables and in some statistical formulas. | Expert | 2007 or earlier | No |
COT | Returns the cotangent of an angle in radians. | Expert | 2013 | No |
COTH | Returns the hyperbolic cotangent, mainly for technical models. | Expert | 2013 | No |
CSC | Returns the cosecant of an angle in radians. | Expert | 2013 | No |
CSCH | Returns the hyperbolic cosecant. Rarely needed outside maths work. | Expert | 2013 | No |
DECIMAL | Converts a binary, hex or other-base text string into a normal number. It is the reverse of BASE. | Expert | 2013 | No |
DEGREES | Converts radians to degrees, often to make trig results readable. | Intermediate | 2007 or earlier | No |
EVEN | Rounds up in size to the next even whole number, for example to pack items in pairs. | Intermediate | 2007 or earlier | No |
EXP | Raises the constant e to a power, used in growth, decay and continuous compounding. | Intermediate | 2007 or earlier | No |
FACT | Multiplies all whole numbers from 1 up to the input. A building block for counting arrangements. | Intermediate | 2007 or earlier | No |
FACTDOUBLE | Multiplies number x (number-2) x (number-4) and so on. For 7 that is 7 x 5 x 3 x 1. | Expert | 2007 or earlier | No |
FLOOR.MATH | Rounds down to a chosen multiple, like the previous 5 or 0.5. Negative numbers get a predictable direction. | Intermediate | 2013 | No |
FLOOR.PRECISE | Always rounds toward negative infinity, no matter the signs involved. | Intermediate | 2010 | No |
GCD | Finds the greatest common divisor, useful for simplifying ratios and fractions. | Intermediate | 2007 or earlier | No |
INT | Rounds down to a whole number. Remember it moves negative numbers further from zero. | Beginner | 2007 or earlier | No |
ISO.CEILING | Behaves like CEILING.PRECISE and exists mainly for standards-compliant workbooks. | Expert | 2010 | No |
LCM | Finds the least common multiple, for example when two schedules repeat every 4 and 6 days and you want when they line up. | Intermediate | 2007 or earlier | No |
LN | Returns the power e must be raised to in order to get the number. Used in growth rates and log-transformed data. | Intermediate | 2007 or earlier | No |
LOG | Answers 'what power of the base gives this number?', for example how many doublings to reach a target. | Intermediate | 2007 or earlier | No |
LOG10 | Returns the power of 10 that equals the number, handy for orders of magnitude. | Intermediate | 2007 or earlier | No |
MDETERM | Computes the determinant of a square matrix, used to test whether a system of equations has a unique solution. | Expert | 2007 or earlier | No |
MINVERSE | Inverts a square matrix, a step in solving simultaneous equations or regression by hand. | Expert | 2007 or earlier | Yes |
MMULT | Multiplies two matrices. Used for weighted portfolios, transformations and linear algebra in a sheet. | Expert | 2007 or earlier | Yes |
MOD | Gives the leftover after dividing. Common for odd/even tests, every-nth-row formatting and wrapping around a cycle. | Beginner | 2007 or earlier | No |
MROUND | Rounds to the closest multiple, such as nearest 5 minutes or nearest 0.05 in pricing. | Intermediate | 2007 or earlier | No |
MULTINOMIAL | Counts the ways to split a group into labelled sub-groups of given sizes. | Expert | 2007 or earlier | No |
MUNIT | Builds an identity matrix of a given size for use in matrix calculations. | Expert | 2013 | Yes |
ODD | Rounds up in size to the next odd whole number. | Intermediate | 2007 or earlier | No |
PERCENTOF | Shows what share one group makes up of the total, replacing the SUM(x)/SUM(y) pattern. Designed to pair with GROUPBY and PIVOTBY. | Intermediate | 365 only | No |
PI | Returns pi to about 15 digits. Use it with RADIANS, area and circumference formulas. | Intermediate | 2007 or earlier | No |
POWER | Raises a number to a power. It does the same job as the ^ operator. | Beginner | 2007 or earlier | No |
PRODUCT | Multiplies a list of numbers or a range, such as compounding growth factors. | Beginner | 2007 or earlier | No |
QUOTIENT | Integer division, such as how many full boxes you can fill. Pair with MOD for the remainder. | Intermediate | 2007 or earlier | No |
RADIANS | Converts degrees to radians, needed before using SIN, COS or TAN. | Intermediate | 2007 or earlier | No |
RAND | Generates a fresh random number every time the sheet recalculates. Useful for sampling, simulations and shuffling. | Intermediate | 2007 or earlier | No |
RANDARRAY | Fills a whole grid with random values in one formula, for test data or random sampling. | Intermediate | 2021 | Yes |
RANDBETWEEN | Picks a random integer in a range, like a dice roll or a random ID. | Intermediate | 2007 or earlier | No |
ROMAN | Turns an ordinary number into Roman numerals, often for labels or outlines. | Expert | 2007 or earlier | No |
ROUND | Standard rounding, with halves going away from zero. Use it to fix a displayed total to its real value, not just its formatting. | Beginner | 2007 or earlier | No |
ROUNDDOWN | Always rounds toward zero, effectively chopping digits off. | Beginner | 2007 or earlier | No |
ROUNDUP | Rounds up in size whenever there is any remainder, for example billing by whole units. | Beginner | 2007 or earlier | No |
SEC | Returns the secant of an angle in radians. | Expert | 2013 | No |
SECH | Returns the hyperbolic secant, used in a few technical models. | Expert | 2013 | No |
SEQUENCE | Generates number lists such as 1 to 12 or a series of dates, without typing them. A building block for many dynamic array formulas. | Intermediate | 2021 | Yes |
SERIESSUM | Adds up a polynomial or power series from a list of coefficients, as in Taylor expansions. | Expert | 2007 or earlier | No |
SIGN | Shows the direction of a number, handy for flagging increases and decreases. | Intermediate | 2007 or earlier | No |
SIN | Returns the sine of an angle. Convert degrees with RADIANS first. | Expert | 2007 or earlier | No |
SINH | Returns the hyperbolic sine, used in technical formulas. | Expert | 2007 or earlier | No |
SQRT | Finds the square root, for example for standard deviation or side length from an area. | Beginner | 2007 or earlier | No |
SQRTPI | Gives sqrt(number x PI), a shortcut that turns up in statistics formulas. | Expert | 2007 or earlier | No |
SUBTOTAL | Totals a filtered list so the result changes with the filter. It also skips other SUBTOTAL results, preventing double counting. | Beginner | 2007 or earlier | No |
SUM | Adds numbers together. The most-used function in Excel. | Beginner | 2007 or earlier | No |
SUMIF | Adds up values that match one condition, such as sales for one region. | Beginner | 2007 or earlier | No |
SUMIFS | Adds values subject to several conditions at once, e.g. sales for a product in one quarter. | Beginner | 2007 or earlier | No |
SUMPRODUCT | Multiplies lists item by item and adds the results. Also a flexible way to count or sum with multiple conditions. | Beginner | 2007 or earlier | No |
SUMSQ | Squares each value and adds them, a step in variance and distance calculations. | Intermediate | 2007 or earlier | No |
SUMX2MY2 | Adds up the differences of squares of paired values. A statistics helper. | Expert | 2007 or earlier | No |
SUMX2PY2 | Adds up the sums of squares of paired values, as used in some statistical formulas. | Expert | 2007 or earlier | No |
SUMXMY2 | Totals (x - y) squared across pairs, the core of a sum-of-squared-errors calculation. | Expert | 2007 or earlier | No |
TAN | Returns the tangent of an angle in radians. | Expert | 2007 or earlier | No |
TANH | Returns the hyperbolic tangent, seen in machine-learning style squashing curves. | Expert | 2007 or earlier | No |
TRUNC | Removes decimals without rounding, so 8.97 becomes 8.9 at one place. | Intermediate | 2007 or earlier | No |
Other categories
- Lookup and reference functions
43 functions
- Logical functions
19 functions
- Text functions
48 functions
- Date and time functions
25 functions
- Statistical functions
111 functions
- Financial functions
56 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions