Absolute in Excel

Absolute in Excel

This function calculates the value of a number, irrespective of whether it is positive or negative.

Microsoft Excel Function

Syntax =ABS(CellAddress or Number)

                   Number       Absolute Value                                            

                   15      15      =ABS(C4)                                

                   -18    18      =ABS(C5)                                

                   1.25   1.25   =ABS(C6)                                

                   -1.25 1.25   =ABS(C7)                                

Formatting                                                           

The result will be shown as a number, no special formatting is needed.                                                                                                                                            

Example                                                               

The following table was used by a company testing a machine which cuts timber.

 The machine needs to cut timber to an exact length.                                                                

Three pieces of timber were cut and then measured.                                                                

In calculating the difference between the Required Length and the Actual Length it does not matter if the wood was cut too long or short, the measurement needs to be expressed as  an absolute value.                                                           

Table 1 shows the original calculations.                                                                

The Difference for Test 3 is shown as negative, which has a knock on effect when the Error Percentage is calculated. Whether the wood was too long or short, the percentage should still be expressed as an absolute value

Table 1

"Test

Cut"   "Required

Length"       "Actual

Length"       Difference   "Error

Percentage"         

                   Test 1          120    110    10      8%             

                   Test 2          110    110    0        0%             

                   Test 3          120    150    -30    -25%          

                                                =D36-E36                     

                                                                            

Table 2 shows the same data but using the =ABS() function to correct the calculations

                   Table 2                                                       

                   "Test

Cut"   "Required

Length"       "Actual

Length"       Difference   "Error

Percentage"         

                   Test 1          100    100    0        0%             

                   Test 2          120    90      30      25%           

                   Test 3          120    150    30      25%           

                                                =ABS(D45-E45)