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.
SUMPRODUCT function returns the result of multiplied and summed arrays.
= SUMPRODUCT(array1, [array2], [array3], …)
The first array argument whose components you want to multiply and then add.
Array arguments 2 to 255 whose components you want to multiply and then add.
Array arguments 2 to 255 whose components you want to multiply and then add.
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 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.
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.
= TRUNC (number, [num_digits])
The number you want to truncate.
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.
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.
The required number is raised to exponential.
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 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.
CEILING.MATH function returns the rounded value of a positive or negative number.
= CEILING.MATH(number, [significance], [mode])
Numbers need to be rounded. The number must be less than 9.99E+307 and greater than -2.229E-308.
The multiple to which Number is to be rounded.
For negative numbers, you can select whether the number to be rounded away or far from zero.
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.
ARABIC function returns a number in Arabic numerical format.
The Numbers that you want to be changed to Arabic numbers.
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 (To be created as a table)
Purpose of Excel AGGREGATE Function
To return aggregate calculation.
The AGGREGATE function returns a value based on function specified.
A number 1 to 19 that specifies which function to use.
A numerical value that determines which values to ignore in the evaluation range for the function.
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
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.
FACTDOUBLE function returns the double factorial of a number.
The number for which to get double factorial.
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.
FLOOR.MATH function will return a rounded number.
The number to be rounded down.
The multiple of significance to round the supplied number to.Default is 1.
The numeric argument that reverses the direction of rounding for negative numbers only. Default is 0.
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.