Examples ABS function in Excel for converting the value modulo

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



Examples of using ABS function in Excel

Example 1. Determine the length of the projection of the segment on the axis Ox, which is given by the coordinates of the initial (-7,-4) and final (19,44) points.

Initial data:

Example 1.

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

ABS.

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 magnitude of the projection on the axis Oy. Results:

difference.

As a result of calculating the formula modulo, the projection of the segment onto the axis Ox was determined.



Calculate the difference between the smallest negative and positive number

Example 2. Find the difference between the lowest and the lowest absolute values of the array elements.

Initial data:

Example 2.

For the calculation, we use the following (formula array CTRL + SHIFT + Enter):

The MIN function searches for the lowest value in the range. To find the smallest non-negative number, use the expression =MIN(ABS(B3:B12)).

The result:

results.

Excel modulo sum for negative numbers

Example 3. In the Excel spreadsheet recorded all the transactions of the enterprise for a certain period of time. Negative values correspond to expense operations. Calculate the amount of expenses for the specified period.

Initial data:

Example 3.

For calculation, use the following array formula (CTRL + SHIFT + Enter):

Algorithm of calculations:

  1. When searching the elements of the range B3:B12, each value is checked for belonging to the range of negative numbers.
  2. SUM function returns the sum of absolute values (due to the use of the ABS function) taken from a range of negative numbers selected.
  3. If there are no negative numbers (expense operations), the value 0 (zero) will be returned.

Calculation results:

modulo sum.

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

Features of using ABS function in Excel

The function has the following syntax notation:

=ABS(number)

The only argument of this function (mandatory) is a number. It characterizes a real number, which will be determined by the absolute value.

Notes 1:

  1. ABS function accepts arguments that are numeric values, textual representations of numbers, or data of a logical type (TRUE, FALSE).
  2. If a text string was passed as an argument to the function in question, which cannot be converted to a numeric data type (for example, =ABS(“text”)), the result will be the error code #VALUE!.
  3. This function can be used as an array formula for working with large data ranges.

Notes 2:

  1. The number modulus allows one to determine any physical quantity that can be mathematically defined by a negative number, but in the real world it is represented only by an absolute (non-negative) value, for example, the length of a certain segment.
  2. When writing a macro, you may also need to calculate the absolute values of some numbers. In VBA, the function ABS() of the same name is used (as in many other programming languages).
  3. In Excel, there are two alternative ways to get the absolute value of a certain value:

Download examples ABS for converting value modulo in Excel

  • using the SQRT and POWER functions with exponent 2. For example, the result of calculations =SQRT(POWER (A1,2)) will always be a number from a range of positive values (assuming that cell A1 contains data of a numeric type);
  • using the IF function. For example, to return the modulus of the number -5, you can use the entry =IF(A1<0,A1*(- 1),A1), where A1 is the cell in which the value -5 is stored.

en ru