Modulus is the positive value of any given number. The value is not negative even for numbers with a minus sign, while for a positive number it is always equal to it. The formula is simple, and when working with tabular data, you can immediately enter it into the field or use the “Function Wizard” for calculations.
Let’s consider how the calculation is performed in the Excel spreadsheet processor from the Microsoft office suite and what tools are used for this.
Contents
Features of the application of the function
The modulus of a number is calculated in Excel using the main tool – ABS, but other built-in tools can be used (we will consider the methods below).
The ABS function is written in the following form: =ABS(number), and the number is the obligatory and only argument pointing to a certain real number, the absolute value of which is to be found.
Features of using ABS:
- The function accepts arguments in the form of numeric values, text representations of numbers, or boolean data (for example, TRUE, FALSE).
- If the function argument is a text string that cannot be converted to a numeric value (for example, =ABS(number)), then the execution result will be a #VALUE! error.
- ABS can be applied as an array formula to interact with volumetric data.
The reasons for looking for an absolute value can be different. So, it is often used to calculate the length of segments, including temporary ones, when a negative value is calculated. The calculation may also be required when writing a macro.
It is clear from the definition that the modulus will always be positive, regardless of the presence of a negative sign in front of the digit and, in fact, the value of the same number. That is, the modulus of -5 will be equal to 5, while the modulus of 5 is also equal to 5. Write the definition with the formula: |-a| = |a| = a. So, in the case of a vector with a value of -5, the minus sign indicates its opposite direction, then to find the length of the directed segment, you will need to calculate the module, since the distance between points cannot also be determined by a negative value.
Using ABS
When figuring out how to put a module in Microsoft Excel, it’s worth starting with the main function designed for this task. Its name is an abbreviation and comes from the English. Absolute (“absolute”).
Standard option
The formula for calculating the module in Excel might look like this:
- ABS(number);
- ABS(cell_address_with_number).
You can insert formulas in two ways. Consider how to calculate the module in Excel using each of the options.
Method 1:
- we enter in the cell or line the value for which we are looking for the module. For example, let’s find it for -7, then you need to write “=ABS(-7)”;
- press the Enter key and in the indicated cell we get the result of the calculation.
Method 2. If you don’t want to write formulas manually, you can use the convenient Excel tool – the “Function Wizard”:
- click on the cell (the result will be saved in it), click the “Insert function” button (denoted as fx);
- in the “Function Wizard” window, select “ABS” from the available options, click “OK”;
- in the window that opens to enter the function argument, enter the required number in the “Number” column;
- if you need to take a number from those that are already in the table, click on the button to the right of the input line – the window will collapse, and then click on the cell where the required number is located. The argument will be added, again press the button to the right of the input line and get into the “Function Arguments” window, where the line will already be filled;
- specify the address of the cell (or select it by clicking in the table), click “OK”;
- in the selected cell we get the result of the calculations;
- if it is necessary to calculate the entire column, the module formula can also be dragged to other cells, for which we hover over the cell with the already calculated module, when the “+” icon appears, hold down the LMB and drag down to the desired cell;
- in the entire column or in individual cells selected with the mouse, we will get the result.
ABS in formulas
The use of the function is not limited to the search for a module, using it in combination with formulas, you can perform tasks of a different nature. Consider the example of determining the difference between the smallest value with a minus sign and a positive one:
- a data array has been created;
- in the line of formulas we write the expression: {MIN(ABS(B3:B12))}. Curly braces here speak of interaction with an array, and they should be set by using the hot keys Ctrl + Shift + Enter after writing the formula, then the modulus is calculated in each cell in the range B3-B12 (without using arrays, such an entry provokes an error).
Application example:
Calculation examples
Let’s look at possible examples of using ABS, including in formulas.
Example 1. Determining the projection of a segment on the abscissa (X) and ordinate (Y) axes
From the condition of the problem, the coordinates of the segment A (-12; -10) and B (-27; -36) are known. Excel does the following for a solution:
- fill in the table with the available information;
- using ABS, we calculate the projection on the X axis (indicate the numbers of cells B4-B2);
- to calculate the projection on the Y axis, specify cells B5-B3.
As a result, we get:
Example 2. Calculating the expenses of an enterprise
The task is to sum up the costs for a certain period of time by adding negative values (they represent costs) modulo. In Excel, it’s easy:
- create an array of data;
- write the formula in the corresponding field: {=SUM(IF(B3:B12<0;ABS(B3:B12);0))}. We put curly brackets by pressing Ctrl + Shift + Enter at the end of the formula, indicating working with an array;
- data analysis reveals the presence of negative numbers, their modulus is calculated, and the data for the specified period are summarized, while plus values are not taken into account.
The result looks like this:
Other options for obtaining an absolute value
In addition to the standard ABS tool, there are some other methods on how to calculate the modulus value in Excel using alternative built-in tools that denote mathematical operations. They are based on the laws of mathematics and make it easy to perform a given task if a special function has not been used.
SIGN
The idea is to multiply negative numbers by -1 and positive numbers by one. The formula will return a value depending on the original number. So, by multiplying the result by the original number in Excel, we get the absolute value modulo. If a negative number is taken, the result of the multiplication will be positive.
ROOT
Since the square root cannot be negative, this method can also be used to derive the absolute value. The tool will allow you to make a module by squaring the original number and extracting the root from it.
IF
One of the most popular and simple functions, it can be used to perform logical comparisons of values and results. Using an operator is also a good way to accomplish the task.
If the value is negative, then the multiplication by -1 is performed, otherwise nothing happens.
VBA language
Through ABS, the module is found in most programming languages, including VBA. In a Visual Basic module, the query might be: A=ABC(-7). Here element A is assigned the number 7 and ABS is used to get the absolute value.
Finding a module in Excel is not difficult, regardless of the version of the program, and a user with any skill level can use a special tool or additional tools suitable for the same purpose and working on the basis of mathematical laws, as you can see from personal experience.