Scribd
Upload
11 views10 pages

Excel Mathematical Functions Overview

The document discusses several common mathematical functions in Excel including ABS, MOD, POWER, PRODUCT, SQRT and SUM. It provides the syntax and examples of using each function as a worksheet formula.

Uploaded by

imadonti.mca
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views10 pages

Excel Mathematical Functions Overview

The document discusses several common mathematical functions in Excel including ABS, MOD, POWER, PRODUCT, SQRT and SUM. It provides the syntax and examples of using each function as a worksheet formula.

Uploaded by

imadonti.mca
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Chapter 4 Mathematical functions

Excel has over 300 functions grouped by type. There are more than 60
mathematical and trigonometric functions listed.

1. Mathematical functions:

An Excel function is a predefined formula, performing a specific calculation using


user-entered values as arguments. Each Excel function has a different role and
allows you to calculate a specific value.

2. Access to mathematical functions


Click on the “formulas” tab or the symbol and you see the Excel functions library.
Scroll tab “or select a category “ and choose “Math & Trig”
Which gives you a drop-down menu of functions in alphabetical order.

List of matematicals functions

Some of these functions are quite easy to understand - the PI function returns the
value of the constant when dealing with geometry circles (3.14). Some are obvious
from context.
SIN() and COS() are basic trigonometric functions.
3. List of mathematical functions

Among the mathematical functions we have chosen the most common ones.
3.1 ABS Function.

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 1
Chapter 4 Mathematical functions

Returns the absolute value of a number.


The absolute value of a number is the number without its sign.
Syntax
ABS (number)
The ABS function syntax contains the following arguments:
 Number : Mandatory. Represents the real number whose absolute value you
want to obtain.
Example
A2=-4

Formula Description Result


=ABS(2) absolute value of 2 2
=ABS(-2) absolute value of -2 2
=ABS(A2) absolute value of -4 4

3.2 MOD function:

The MOD function in Excel is used to find remainder after division of one number
(dividend) by another (divisor). In mathematics, this operation is called
the modulo operation or modulus, hence the name MOD.

Syntax

The MOD function has the following syntax: MOD (number, divisor)

 Number Required. The number for which you want to find the remainder (the
number to be divided).
 Divisor Required. The number by which you want to divide number (the
number to divide by).

For example MOD (10,3) returns 1 because 10 divided by 3 has a quotient of 3 and
leaves a remainder of 1 (10=3*3+1). The formula MOD (10,5) returns zero because
10 is divided by 5 without remainder.

Remarks :
1. The MOD function returns a numeric value.
2. The result of the MOD function has the same sign as the divisor.
3. If the divisor is 0, MOD returns the #DIV/0! error because you cannot divide by zero.
4. If the number or divisor is a text value, an Excel Mod formula returns the #VALUE! error.

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 2
Chapter 4 Mathematical functions

5. The MOD function can be expressed in terms of the INT function:

MOD (n, d) = n - d*INT(n/d).

Example (as Worksheet Function)


Let's look at some Excel MOD function examples and explore how to use the MOD
function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following MOD examples would return:

=MOD(A1, A2) Result: 2


=MOD(A1, A3) Result: -6
=MOD(34, A2) Result: 0
=MOD(34, 0) Result: #DIV/0!

3.3 POWER Function:


The Basics of the POWER Function in Microsoft Excel

The POWER function in Microsoft Excel computes a given numerical value raise to a
supplied exponent or power and The POWER function returns a numeric value.
Syntax
The syntax of the POWER function is:
POWER (number, power)
From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 3
Chapter 4 Mathematical functions

 Number Required. The base number. It can be any real number.


 Power Required. The exponent to which the base number is raised.

In this formula, the power and number arguments are number values. These
arguments can be entered in the formula either as a cell reference which contain the
numeric value or as a value returned from other formulas or functions.
But you have to take note that the POWER function in Microsoft Excel is similar to
the power operator (^). For example, POWER (6, 2) is similar to 6^2.

Remark:
However, if you have an error from the POWER function, the function will return the
result which is #VALUE!. This result is displayed if Microsoft Excel is not able to
interpret either of the number or power arguments as numeric values.
Example : (as Worksheet Function)
Let's look at some Excel POWER function examples and explore how to use the
POWER function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following POWER examples would
return:

=POWER(A1, A2) Result: 81


=POWER(A1, A3) Result: 140.2961154
=POWER(A2, 2) Result: 16

3.4 PRODUCT function :

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.

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 4
Chapter 4 Mathematical functions

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.
Syntax
The syntax for the PRODUCT function in Microsoft Excel is:
PRODUCT ( number1, number2, …..number_n )
The PRODUCT function syntax has the following arguments:

 number1 Required. The first number or range that you want to multiply.
 number2, ... Optional. Additional numbers or ranges that you want to
multiply, up to a maximum of 255 arguments.
Remarks:
 The PRODUCT function returns a numeric value.
 If an argument is an array or reference, only numbers in the array or
reference are multiplied. Empty cells, logical values, and text in the array
or reference are ignored.
Example1 : (as Worksheet Function)
Let's look at some Excel PRODUCT function examples and explore how to use the
PRODUCT function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following PRODUCT examples would return:

=PRODUCT(A1, A2) Result: 12


=PRODUCT(A1, A2, A3) Result: 54
=PRODUCT(A1, A2, A3, A4) Result: -366.12
=PRODUCT(A1, A2, A3, A4, -2) Result: 732.24

3.5 SQRT Function :

The Microsoft Excel SQRT function returns the square root of a number.
Syntax : The syntax for the SQRT function in Microsoft Excel is:
SQRT( number )
 Number : A positive number that you wish to return the square root for.
Remarks:
- The SQRT function returns a numeric value.
- If a negative number is entered in the number parameter, the SQRT function
will return the #NUM! error.

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 5
Chapter 4 Mathematical functions

Example (as Worksheet Function):


Let's look at some Excel SQRT function examples and explore how to use the SQRT
function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SQRT examples would return:

=SQRT(A1) Result: 5
=SQRT(A2) Result: 5.796550698
=SQRT(A3) Result: #NUM!
=SQRT(82.6) Result: 9.088454214

3.6 SUM Function :

You can use a simple formula to sum numbers in a range (a group of cells), but the SUM
function is easier to use when you’re working with more than a few numbers. For
example =SUM(A2:A6) is less likely to have typing errors than =A2+A3+A4+A5+A6.

The SUM function adds values. You can add individual values, cell references or ranges
or a mix of all three.

Here’s a formula that uses two cell ranges: =SUM(A2:A4,C2:C3) sums the numbers
in ranges A2:A4 and C2:C3. You’d press Enter to get the total of 39787.

To create the formula:


1. Type =SUM in a cell, followed by an opening parenthesis (.
2. To enter the first formula range, which is called an argument (a piece of data the
formula needs to run), type A2:A4 (or select cell A2 and drag through cell A6).
3. Type a comma (,) to separate the first argument from the next.
4. Type the second argument, C2:C3 (or drag to select the cells).

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 6
Chapter 4 Mathematical functions

5. Type a closing parenthesis ), and then press Enter.


Each argument can be a range, a number, or single cell references, all separated by
commas.
 =SUM(A2:A4,2429,10482)
 =SUM(4823,A3:A4,C2:C3)
 =SUM(4823,12335,9718,C2:C3)
 =SUM(A2,A3,A4,2429,10482)
Tip: If you need to sum columns or rows of numbers next to each other, use
AutoSum to sum [Link] a cell next to the numbers you want to sum,
click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM
function) to sum the numbers.

Example : (as Worksheet Function)


Let's look at some Excel SUM function examples and explore how to use the SUM
function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SUM examples would return:

=SUM(A2:A6) Result: 231.2


=SUM(A2, A3) Result: 17.7
=SUM(A3, A5, 45) Result: 57.6
=SUM(A2:A3, A5:A6) Result: 31.2
=SUM(A2:A3, A5:A6, 500) Result: 531.2
3.7 SUMIF Function

Description

You use the SUMIF function to sum the values in a range that meet criteria that
you specify.

For example, suppose that in a column that contains numbers, you want to sum
only the values that are larger than 5. You can use the following formula:

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 7
Chapter 4 Mathematical functions

=SUMIF(B2:B25,">5")
In this example, the criteria is applied the same values that are being summed. If
you want, you can apply the criteria to one range and sum the corresponding
values in a different range.
For example, the formula C9 =SUMIF(B2:B8; B3;C2:C8) sums only the values in
the range C2:C8, where the corresponding cells in the range B2:B8 equal "omar."
(B3=”Omar”)

Syntax

=SUMIF(range,criteria,sum_range)

1. Range: Mandatory. Range of cells to calculate based on the criterion.


 The cells in each range must be numbers or names, matrices, or
references containing numbers.
 Empty or text values are not taken into account.
 The selected range can contain dates in standard Excel format:
2. Criteria: Mandatory. Criteria, expressed as a number, expression, cell
reference, text, or function that defines the cells to add. Wildcards can
be included:
 a question mark (?) to match any character;
 an asterisk (*) to match any sequence of characters.
 To actually search for a question mark or asterisk, type a tilde (~) in
front of that character.
For example, the criteria can be expressed as 32, ">32", B5, "3?" », “apple*”,
“*~ ? » or TODAY().
Important: All textual criteria and all criteria that contain mathematical or
logical symbols must be enclosed in quotation marks ("). However, quotation
marks are not necessary for numeric criteria.

3. sum_range : Optional. Actual cells to add, if you want to add cells other
than those specified in the range argument. If the sum_range argument is
omitted, Excel adds the cells specified in the range argument (the cells to
which the criterion applies).

For more explanation, use this video “video sumif”.


Remark
The fields of use of the [Link] function are extremely wide, it can be used in
inventory calculations, but also has accounting and financial applications.

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 8
Chapter 4 Mathematical functions

To go further, I strongly encourage you to look at the [Link] function which makes it
easier to share by multiplying the [Link] function over several ranges and several criteria.

4. Application example for SUMIF function


Find the sum of Total stats for Grass type:
The condition is that the type is "Grass".

Example SUMIF function, step by step:


1. Select the cell F3
2. Type =SUMIF
3. Double click the SUMIF command

4. Specify the range for the conditionB2:B10 (the Type 1 values) [Link] ,
[Link] the criteria (the cell E3, which has the value "Grass") [Link] ,
[Link] the range for the sum C2:C10 (the Total values) [Link] enter

The function now sums the Total stats for Grass type.
The function can be repeated for the other types to compare them:

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 9
Chapter 4 Mathematical functions

Now, we can see the sum of total stats for the different types:

Bibliography

1. Help Microsoft EXCEL


2. Walter Learning
[Link] › blog › bureautique
3. [Link]

From the achievements: Pr. Baouni Lila & Dr. Kaddour Daouia Page 10

Common questions

Powered by AI

The MOD function in Excel is used to find the remainder after the division of one number (dividend) by another (divisor). The result of the MOD function retains the same sign as the divisor. If the divisor is zero, the function returns a #DIV/0! error because division by zero is undefined . Moreover, if either the number or divisor is non-numeric, the function returns a #VALUE! error .

Both the SQRT and POWER functions in Excel return a #NUM! error when they receive invalid numeric inputs. The SQRT function encounters this error if a negative number is provided because the square root of a negative number is not defined within real numbers in Excel . Similarly, the POWER function can return a #VALUE! error if either the number or power arguments are not recognized as numeric, though the primary #NUM! error occurs when you try computing a power that results in an undefined or unrepresentable number in the context of real numbers like raising a negative number to a fractional power .

AutoSum enhances productivity by automatically generating a SUM formula that captures adjacent rows or columns with numerical data, eliminating the need to individually specify cell ranges. It is ideal for quickly totaling datasets with contiguous numeric entries, allowing for immediate results with minimal input and reducing the chance for manual calculation errors. AutoSum should be used when the range of numbers to sum is straightforwardly aligned in adjacent cells, such as end-of-report totals or routine financial summaries, streamlining common tasks .

Using the Power Operator (^) is preferable when a more concise formula is desired, especially for straightforward power computations where the operation is clear and simple. For example, 6^2 is more concise than POWER(6, 2), which can improve formula readability in contexts involving simple arithmetic operations. However, in more complex calculations where clarity around function usage is necessary, the POWER function might be the better choice for documentation purposes .

The SUMIF function is more beneficial than the standard SUM function when there is a need to conditionally sum values based on specific criteria. For instance, in a sales spreadsheet where a user intends to sum total sales only from a particular region or product category, SUMIF can efficiently compute this by evaluating the criteria within a column while summing corresponding values in another column. This selective summation simplifies analysis and removes the need for additional filtering or manual partitioning of data .

The MOD function can be expressed using the INT function with the equation MOD(n, d) = n - d*INT(n/d). This expression reveals that the MOD function calculates the modulus by first computing the integer quotient of n divided by d using the INT function, then multiplying this quotient by d, and finally subtracting this product from n to find the remainder .

The SUMIF function allows users to sum values within a range based on specific criteria. The function's syntax =SUMIF(range, criteria, sum_range) involves specifying a range to evaluate, a criterion to apply, and an optional range to sum. It can be used for filtering sums based on conditions such as dates, specific text, or numeric limits. Potential applications include summarizing financial data according to budget categories or calculating inventory based on quantity thresholds, enhancing efficiency in business analytics .

The ABS function in Excel returns the absolute value of a number, which means it negates any negative sign. For example, ABS(-4) returns 4, just as ABS(4) would return 4 as well. This can be crucial in data analysis, particularly in situations where negative deviations need to be treated positively, such as calculating standard deviations or error terms .

The PRODUCT function multiplies all numbers given as arguments and can handle up to 255 parameters, effectively multiplying numbers across a range of cells. It is more advantageous over the '*' operator when dealing with large arrays or ranges where manual multiplication would be cumbersome and more prone to errors. The PRODUCT function also ignores non-numeric cells, making it more robust for large datasets with mixed data types .

The SUM function in Excel offers several advantages over typing arithmetic expressions directly. It reduces the likelihood of errors since you need only specify the ranges or numbers, rather than manually adding each number or cell reference. This is particularly beneficial when summing large groups of cells as it enhances readability and reduces the complexity of the formula. The SUM function is also more adaptable to changes; adding or removing entries within a specified range automatically updates the sum .

You might also like