Tag

MATH/TRIG FUNCTIONS

Browsing

The Excel SUMPRODUCT is categorized under Math & Trignomentry functions. SUMPRODUCT function multiplies the arrays and returns the sum of products. By default Excel operation is multiplication but addition, subtraction, and division are also possible. SUMPRODUCT can also be used as a substitute to COUNTIFS or SUMIFS, with more feasibility. To increase or extend the functionality of any function it can be used inside SUMPRODUCT.

Purpose of Excel SUMPRODUCT Function

To multiply and the sum arrays.

Return value

SUMPRODUCT function returns the result of multiplied and summed arrays.

Syntax

= SUMPRODUCT(array1, [array2], [array3], …)

Arguments

ArgumentsDescriptionRemarks
array1The first array argument whose components you want to multiply and then add. Mandatory
[array2] Array arguments 2 to 255 whose components you want to multiply and then add. Optional
[array3]Array arguments 2 to 255 whose components you want to multiply and then add. Optional

Usage notes

  • The array arguments should have the same dimensions else, SUMPRODUCT returns the #VALUE! error value. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error since the ranges aren’t the same size.
  • SUMPRODUCT treats non-numeric array entries as if they were zeros.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The Excel MOD function is categorized under Financial functions. MOD function returns the remaining portion after dividing two numbers. The result of MOD carries the same sign as the divisor.

Purpose of Excel MOD Function

To get a reminder from division.

Return value

This function returns the remainder.  Example: Mod of 17 when divided by 3, is 2 (as 2 is the reminder).

Syntax

= MOD(number, divisor) 

Arguments

ArgumentsDescriptionRemarks
numberThe number you want to find the remainder. Mandatory
divisorThe number you want to divide number. Mandatory

Usage notes

  • If the divisor is 0, MOD returns the #DIV/0! error value.
  • This function is often seen in formulas that deal with “every nth” value
  • This function can be expressed in terms of the INT function:
  • MOD(n, d) = n – d*INT(n/d)

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The Excel TRUNC function is categorized under Math & Trignomentry functions. This function helps convert fraction to integer or in simple words we can say that it removes fraction part of the number which will be truncated to integers.

Purpose of Excel TRUNC Function

To truncate a number to a given precision.

Return value

This function returns the truncated value. for example,  this function returns the value of 2.896 when we truncate the number 2.8965412 to 3 digits.

Syntax

= TRUNC (number, [num_digits])

Arguments

ArgumentsDescriptionRemarks
numberThe number you want to truncate. Mandatory
[num_digits]A number specifying the precision of the truncation. The default value for num_digits is 0 (zero). Optional

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The Excel EXP stands for the exponential function, it is categorized under financial functions.EXP function supports to calculate the exponent raised to any number we provide. This function is also termed as the base for the natural logarithm.

Purpose of Excel EXP Function

To find the value of e-raised to the power of a number.

Return value

EXP function returns the constant e raised to the power of a number. The constant “e” equals 2.71828182845904, the base of the natural logarithm.

Syntax

= EXP(number)

Arguments

ArgumentsDescriptionRemarks
numberThe required number is raised to exponential. Mandatory

Usage notes

  • It can use the exponential operator, to calculate the power of other bases.
  • EXP is the inverse of LN, the natural logarithm of a number.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The Excel DECIMAL function is categorized under Financial functions. DECIMAL helps convert the alphanumeric text in a given base to decimal values.

Purpose of Excel DECIMAL Function

To convert text representation of a number into decimal numbers.

Return value

DECIMAL function returns the decimal number.

Syntax

= DECIMAL (number, radix)

Arguments

ArgumentsDescriptionRemarks
numberThe text representation of the numbers needs to be converted. Mandatory
radixRadix must be an integer.A number within the range [2, 36] that represents the base of the number to be converted.Mandatory

Usage notes

  • The string length of Text must be less than or equal to 255 characters.
  • The Text argument can be any combination of alpha-numeric characters that are valid for the radix and is not case sensitive.
  • Excel supports a Text argument greater than or equal to 0 and less than 2^53. A text argument that resolves to a number greater than 2^53 may result in a loss of precision
  • Radix must be greater than or equal to 2 (binary, or base 2) and less than or equal to 36 (base 36).
  • A radix greater than 10 uses the numeric values 0-9 and the letters A-Z as needed. For example, base 16 (hexadecimal) uses 0-9 and A-F, and base 36 uses 0-9 and A-Z
  • If either argument is outside its constraints, DECIMAL may return the #NUM! or #VALUE! error value.

Availability

Since 2013 for Windows.
Since 2011 for Mac.

The Excel CEILING.MATH Function is categorized under math & trigonometry functions, wherein it rounds a number to the nearest integer or to a supplied multiple of a significance. Generally, positive numbers are rounded far from zero, negative numbers are rounded near to zero. User has an option of reversing the direction of rounding negative numbers.

Purpose of Excel CEILING.MATH Function

To rounds up the numbers to the nearest integer or supplied multiple of significance.

Return value

CEILING.MATH function returns the rounded value of a positive or negative number.

Syntax

= CEILING.MATH(number, [significance], [mode])

Arguments

ArgumentsDescriptionRemarks
numberNumbers need to be rounded. The number must be less than 9.99E+307 and greater than -2.229E-308. Mandatory
[significance]The multiple to which Number is to be rounded. Optional
[mode]For negative numbers, you can select whether the number to be rounded away or far from zero. Optional

Usage notes

  • Returns #VALUE if the arguments are non-numeric.
  • By default, the significance is +1 for positive numbers and -1 for negative numbers.
  • By default, positive numbers with decimal portions are rounded up to the nearest integer. For example, 9.2 is rounded up to 10.
  • By default, negative numbers with decimal portions are rounded up, neat to 0, to the nearest integer. For example, -7.6 is rounded up to -7.
  • By specifying the significance and mode arguments, you can change the direction of the rounding for negative numbers. For example, rounding -6.3 to a significance of 1 with a mode of 1 round away from 0, to -7
  • The mode argument does not affect positive numbers.
  • The significance rounds the number up to the nearest integer that is a multiple of the significance specified.
  • The exception is where the number to be rounded is an integer. For example, for a significance of 4, the number is rounded up to the next integer that is a multiple of 4.
  • If the number divided by a significance of 2 or greater results in a remainder, the result is rounded up.

Availability

Since 2013 for Windows.
Since 2011 for Mac.

The Excel ARABIC function is categorized under math & trigonometry functions, wherein this function converts Roman numeric text to an Arabic numeric text for example: (VII)Roman Numeric is converted to (7) Arabic Numeric.

Purpose of Excel ARABIC Function

To convert Roman numerical to Arabic numerical.

Return value

ARABIC function returns a number in Arabic numerical format.

Syntax

= ARABIC(TEXT)

Arguments

ArgumentsDescriptionRemarks
textThe Numbers that you want to be changed to Arabic numbers. Mandatory

Usage notes

  • If Text is not a valid value, ARABIC returns the #VALUE! error value.
  • Values that return a #VALUE! error value includes numbers, dates, and text that is not a valid Roman numeral.
  • If an empty string (“”) is used as an input value, 0 is returned
  • The maximum length of the argument is 255 characters. Therefore, the largest number that can be returned is 255,000.
  • The case of the text argument is ignored. For example, “mxmvii” evaluates to the same result (1997) as “MXMVII.”
  • Although a negative Roman number is non-standard, the evaluation of a negative Roman numeral is supported.
  • Insert the negative sign before the Roman text, such as “-MMXI.”
  • Leading and trailing spaces are ignored.

Availability

Since 2013 for Windows.
Since 2011 for Mac.

The Excel AGGREGATE function is categorized under Financial functions. The AGGREGATE function allows us to apply 19 functions (mentioned below arguments) ignoring hidden values, rows or any other nested subtotals.

Function_num                                 Function (To be created as a table)
1AVERAGE
2 COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9VAR.P
10VAR.S
11SUM
12MEDIAN
13MODE.SNGL
14LARGE
15SMALL
16PERCENTILE.INC
17QUARTILE.INC
18PERCENTILE.EXC
19QUARTILE.EXC

Purpose of Excel AGGREGATE Function

To return aggregate calculation.

Return value

The AGGREGATE function returns a value based on function specified.

Syntax

= AGGREGATE(function_num, options, ref1, [ref2], …)

Arguments

ArgumentsDescriptionRemarks
Function_numA number 1 to 19 that specifies which function to use. Mandatory
optionsA numerical value that determines which values to ignore in the evaluation range for the function. Mandatory

Usage notes

  • As soon as you type the function_num argument when you enter the AGGREGATE function into a cell on the worksheet, you will see a list of all functions that you can use as arguments.
  • If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error.
  • If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.

Types of Range:

  • The AGGREGATE 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 option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate

Example

AGGREGATE Function

From the above data, we need to find the max sales and top 3 sales from the given data ignoring the errors and hidden rows.


EX-1 To find MAX


The Formula used: aggregate(4,7,RANGE)
after entering aggregate there will be a list of 19 functions we need to select 4 and press tab which is the maximum function, then click 7 and tab which ignores hidden rows and error values and press enter the function will return the max value.


EX-2 – To find top 3 using a large function

The formula used: aggregate(14,7,RANGE, Referencing or top order)
Note: Giving a reference is a better way
14 allows you to perform a large function
7 ignores hidden rows and error values
Referencing or top-order refers top 3 i.e 1 or 2 or 3

Suggested reading: 20 Excel Functions Useful For Finance Professionals

Availability

Since 20010 for Windows.
Since 2011 for Mac.

Example

Below given related examples are available under our section “Formulas“.

You can view our videos available on our Youtube channel to enhance and refresh your Excel knowledge.

The Excel FACTDOUBLE FUNCTION is used for calculating double factoring. A double factorial is calculated differently for even and odd numbers. The Double Factorial of a number, N, is given by the following:

If N is even: N * (N-2) * (N-4) * … * 4 * 2  If N is odd: N * (N-2) * (N-4) * … * 3 * 1

Purpose of Excel FACTDOUBLE Function

To calculate the double factorial.

Return value

FACTDOUBLE function returns the double factorial of a number. 

Syntax

= FACTDOUBLE(number)

Arguments

ArgumentsDescriptionRemarks
numberThe number for which to get double factorial. Mandatory

Usage notes

  • If the number is in decimal then excel truncates it to integer and then return the value.
  • If the supplied number is < 0,#NUM! error will occur.
  • If the supplied number is non-numeric,#VALUE! error will occur.
  • The FACTDOUBLE is a mathematical function and helps in finding the double factorial.
  • The double factorial, symbolized by two exclamation marks (!!), is a quantity defined for all integers from -1 up to n that have the same parity (odd or even) as n.
  • For an even integer n, the double factorial is the product of all even integers less than or equal to n but greater than or equal to 2. For an odd integer p, the double factorial is the product of all odd integers less than or equal to p and greater than or equal to 1.
  • The double factorial values of 0 and -1 are defined as equal to 1. Double factorial values for integers less than -1 are not defined.
  • Arithmetically the formulas for double factorial are as follows.
  • If n is even, then

n !! = n ( n – 2)( n – 4)( n – 6) … (4)(2)

  • If p is odd, then

p !! = p ( p – 2)( p – 4)( p – 6) … (3)(1)

  • If q = 0 or q = -1, then q !! = 1
  • The double factorial is used in multiple areas like statistics, calculus, and physics.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

The Excel FLOOR.MATH Function rounds down a supplied number to the nearest integer or to the nearest multiple of significance. In MS-Excel this function is categorized under MATH&TRIGNOMETRY functions.

Purpose of Excel FLOOR.MATH Function

To get rounded down the number. Positive decimal numbers are rounded down to the nearest integer and a negative value is rounded down away from zero.

Return value

FLOOR.MATH function will return a rounded number.

Syntax

= FLOOR.MATH(number,[significance],[mode])

Arguments

ArgumentsDescriptionRemarks
numberThe number to be rounded down.Mandatory
[significance]The multiple of significance to round the supplied number to.Default is 1.Optional
[mode]The numeric argument that reverses the direction of rounding for negative numbers only. Default is 0. Optional

Usage notes

  • Positive numbers with decimal portions are rounded down to the nearest integer.
  • For example, 5.2 is rounded down to 5, using the default significance (1).
  • By default, negative numbers with decimal portions are rounded away from 0 to the nearest integer. For example, -5.6 is rounded to -6.
  • By using 0 or a negative number as the Mode argument, you can change the direction of the rounding for negative numbers. For example, rounding -5.4 with a significance of 1 and a mode of -1 rounds toward 0, to -5.
  • The significance argument rounds the number down to the nearest integer that is a multiple of the significance specified. The exception is where the number to be rounded is an integer. For example, for a significance of 3, the number is rounded down to the next integer that is a multiple of 3
  • If the number divided by a significance of 2 or greater results in a remainder, the result is rounded down.

Availability

Since 2013 for Windows.