How to use the SUM function in Excel to add individual values, cell references, ranges, or a mix of all three.
How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.
The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.
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.
How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.
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 in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Rounds a number up, away from 0 (zero).
How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.
Learn more about the SEQUENCE function, which allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. SEQUENCE is in a class of functions called dynamic arrays.
Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Rounds a number down, toward zero.
Rounds a number down to the nearest integer.
The TRUNC functions truncates a number to an integer by removing the fractional part of the number.
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Returns the hyperbolic cosine of a number.
Returns the hyperbolic tangent of a number.
Returns the matrix determinant of an array.
Returns the hyperbolic sine of a number.
Many functions can be approximated by a power series expansion. Returns the sum of a power series based on the formula:
The CEILING.MATH function rounds a number up to the nearest integer or, optionally, to the nearest multiple of significance.
Returns a positive square root.
MROUND returns a number rounded to the desired multiple. 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. The Number and Multiple arguments must have the same sign. If not, a #NUM error is returned.
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.
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.
Returns the square root of (number * pi).
Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
Round a number down to the nearest integer or to the nearest multiple of significance.
Returns a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down. However, if the number or the significance is zero, zero is returned.
The FLOOR function in Excel rounds a specified number down to the nearest specified multiple of significance. Negative numbers are rounded down (further negative) to nearest whole multiple below zero.
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.
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.
Learn about the RANDARRAY function for creating arrays of random numbers between 0 and 1. RANDARRAY is in a class of functions called dynamic arrays.
Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
Returns the double factorial of a number.
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.
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.
PERCENTOF function
RAND 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.
The MINVERSE function returns the inverse matrix for a matrix stored in an array. 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. 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.
The SUMXMY2 function returns the sum of squares of differences of corresponding values in two arrays.
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.
Returns the ratio of the factorial of a sum of values to the product of factorials.
Returns the inverse hyperbolic cosine of a number. The 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.
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.
Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned.
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.
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.
Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
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.
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.
Returns the logarithm of a number to the base you specify.
Returns the sum of the squares of the arguments.
Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.
Returns number rounded up to the nearest odd integer.
Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
Converts degrees to radians.
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.
Returns the cosine of the given angle.
(number, power)
Returns the sine of the given angle.
The MMULT function returns the matrix product or multiplication of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. The way you enter the formula depends on which version of Office 365 you are using.
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.
Returns the base-10 logarithm of a number.
Returns the tangent of the given angle.
Converts radians into degrees.
Converts a number into a text representation with the given radix (base).
Converts an arabic numeral to roman, as text.
Converts a text representation of a number in a given base into a decimal number.
Converts a Roman numeral to an Arabic numeral.
This Excel function returns the sum of the difference of the squares of corresponding values in two arrays.
The MUNIT function returns the unit matrix for the specified dimension.