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

Arguments

Description

Remarks

array1

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

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

Arguments

Description

Remarks

number

The number you want to find the remainder.

Mandatory

divisor

The 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:

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

Arguments

Description

Remarks

number

The 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).

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

Arguments

Description

Remarks

number

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

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

Arguments

Description

Remarks

number

The text representation of the numbers needs to be converted.

Mandatory

radix

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

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

Arguments

Description

Remarks

number

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

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

Arguments

Description

Remarks

text

The 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.”

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)

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

6

PRODUCT

7

STDEV.S

8

STDEV.P

9

VAR.P

10

VAR.S

11

SUM

12

MEDIAN

13

MODE.SNGL

14

LARGE

15

SMALL

16

PERCENTILE.INC

17

QUARTILE.INC

18

PERCENTILE.EXC

19

QUARTILE.EXC

Purpose of Excel AGGREGATE Function

To return aggregate calculation.

Return value

The AGGREGATE function returns a value based on function specified.

A number 1 to 19 that specifies which function to use.

Mandatory

options

A 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

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-1To find MAX

TheFormula 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

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

Arguments

Description

Remarks

number

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

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

Arguments

Description

Remarks

number

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