As in Excele to make a module number. What is a module in Excel - how and what functions it can be calculated

The ABS function finds the absolute value of the number transmitted as an argument, and returns the corresponding value.

Examples of using ABS function in Excel

Determine the length of the projection of the segment on the OX axis, which is set by the coordinates of the initial (-7; -4) and the ultimate (19; 44) points.

Initial data:

To determine the length of the projection on the OX axis, we use the following formula:

B4 and B2 are the initial and final coordinates, respectively. The difference of these coordinates is the desired length of the projection. Similarly, we find the size of the projection on the OY axis. Results:

As a result of calculating the formula for the module, the projection of the segment on the OX axis was determined.

Calculation of the difference between the smallest negative and positive number

Find the difference between the smallest and the smallest absolute values \u200b\u200bof the elements of the array.

Initial data:

To calculate, use the following formula (CTRL + SHIFT + ENTER massif formula):

The Min Function Searches the smallest value in the range. To search for the smallest nonnegative number, the expression is used (ABS (B3: B12)).

The result obtained:

The sum of the module in Excel for negative numbers

IN excel Table All enterprise transactions have been recorded for a certain period of time. Negative values \u200b\u200bcomply with expenditure operations. Calculate the amount of expenses for the specified period.

Initial data:

To calculate, use the following formula of the array (CTRL + SHIFT + ENTER):

Algorithm for calculations:

1. When the elements of the band B3: B12 are checked to belong to the range of negative numbers.
2. The amount function returns the amount of absolute values \u200b\u200b(thanks to the use of the ABS function) taken from the range of selected negative numbers.
3. If there are no negative numbers (expenditure), the value 0 (zero) will be returned.

Results of calculations:

That is, the total expenses of the enterprise for the specified period amounted to 29020.

Features of using the ABS function in Excel

The function has the following version of the syntactic record:

ABS (number)

The only argument of this feature (is mandatory for filling) - a number. He characterizes some total numberwhich will define an absolute value.

Notes 1:

1. The ABS feature takes arguments that are numerical values, textual representations of numbers or logic data (truth, lie).
2. If a text string is transmitted as the argument of the considered function, which cannot be converted to a numeric data type (for example, \u003d ABS ("Text"), the execution result will be an error code # mean!
3. This function can be used as an array formula for working with large data ranges.

Notes 2:

1. The module of the number allows you to determine any physical quantity that mathematically can be defined by a negative number, but in the real world - is represented only by an absolute (non-negative) value, for example, the length of any segment.
2. When writing a macro may also need to calculate the absolute values \u200b\u200bof some numbers. VBA uses the ABS () (as in many other programming languages).
3. In Excel there are two alternative method obtaining absolute value of a certain amount:

• using root functions and degree with an indicator of degree 2. For example, the result of calculations \u003d root (degree (A1; 2)) will always be the number from the range of positive values \u200b\u200b(provided that in the cell A1 contains a numerical type data);
• using a function if. For example, to return the number -5 module, you can use recording \u003d if (A1<0;A1*(-1);A1), где A1 – ячейка, в которой хранится значение -5.

Working in "Excele", you can perform many different tasks, including mathematical. A wide range of program tools allows you to make the required actions that do not require additional applications. One of these functions is the module - in Excel it is used not so often, but it has an important purpose.

What is the module

According to the definition, the module is a mathematical action that returns the absolute value of the number, that is, the number itself without its sign. Despite the specifics, the use of the module can be found not only when solving mathematical problems, but also in economic models, physics and many other accurate sciences.

How to calculate the module in "Excele"?

In order to calculate the module in Excel, you can use in several ways, the simplest of which is the ABS feature. It returns the absolute value of the selected number or mathematical expression.

The syntax of the module function is very simple - in the "Excele" sufficiently set in the formula line "\u003d ABS", after which it is possible to specify the argument in brackets that the number may be referenced to the cell, as well as some function that returns a numeric value. You can also perform this operation by clicking the "Insert function" button where to select the appropriate function, or using the search by specifying the word "module" or "ABS".

Calculation of the sum of modules

One of the common operations in Excel is the sum of the module. It allows you to fold the values \u200b\u200bof the cells without taking into account their sign. To make this action, it is not necessary to initially calculate the module of each number, after which you use the amount function.

When performing an operation associated with several values, an Excel module can work simultaneously with a set or range of cells. So, to calculate the amount of the module, it suffices to use the following query design:

\u003d Amounts (ABS (A1: A4))

Here in the column, and in the first four lines, the values \u200b\u200bare specified for which the module is needed.

Examples

To secure the understanding of the description of the module in Excel and the principles of its work, it is worth considering a couple of simple examples that demonstrate the function.

To calculate the module of a certain number or mathematical example, for example, the difference 2 and 5 should be used by the following entry:

\u003d ABS (2-5)

The result of this request will be the number "3".

If you want to calculate the sum of the modules of the matrix elements, the values \u200b\u200bof which are recorded in the table, with the range A1: C3, the fastest way to find the answer will be the design.

The module is an absolute positive value of any number. Even at a negative number, the module will always be positive. Let's find out how to calculate the magnitude of the module in Microsoft Excel.

ABS feature

To calculate the magnitude of the module in Excel, there is a special feature called "ABS". The syntax of this function is very simple: ABS (number). Either the formula can take this type: ABS (address_children_s_ch). To calculate, for example, the module from the number -8 You need to drive into the string formulas or in any cell on the list the following formula: "\u003d ABS (-8)".

To perform the calculation, press on ENTER - The program issues a positive value in response.

There is another way to calculate the module. It is suitable for those users who are not accustomed to keep various formulas in the head.

It is important to note that some users try to record the module, as is customary in mathematics, that is | (number) |, eg |-48| . But in such a situation, only an error will appear instead of response, since Excel does not understand such a syntax.

In the calculation of the module from the number via Microsoft Excel, there is nothing complicated, since this action is performed using a simple function. The only condition is that this feature you just need to know.

In Excel. Today we will analyze the "Module in Excel" recording formula. The number module is used to determine the absolute value of the number, such as the length of the segment. Below we give several ways to calculate the module of the number in Excel, the main function is ABS, and the additional calculation using functions if the root.

As follows from the definition, the number module is a non-negative number, the value of the number itself. Those. If we have a negative number -7, then in the module it will be 7. The module is written as two vertical lines:

|-7| = 7

What is applied for? If we have the value of the vector equal to -7, where minus denotes its opposite direction, then to find the length of the vector itself, we need to calculate the number module (because the length cannot be a negative value).

Also quite often, the use of the module can be found when calculating the negative time value, but we have separate about it.

In most programming languages, the number module is ABS (from absolute value, ABSolute). Our favorite Excel is no exception.

In the cell we introduce the formula:

As follows from the description of the function, the value of such a formula will be 7.

How to calculate the module via if, an alternative way

In fact, the action of the "Module" function is the definition of a negative number or not and transmitting its value. That is, it is easy to cope the main function of the condition \u003d if ().

If (A1.<0;A1*-1;A1)

I personally always forget about ABS and write via if.

Module number and root

As I suggested, there is a particularly cunning way to find the module of the number through the root function. We will be erected in the second degree and find the root expression.

Root (A1 * A1)

He himself would not have thought.

An example of a file with 3 calculations apply.

Module number in VBA

As we have already told in most programming languages, the module is run by the ABS function. VBA is no exception.

To record the calculation in the code, do the following as follows:

A \u003d ABS (-7)

Here and will be equal to 7.

In general, like this, if something needs to be explained to write in the comments and feel free to.