Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

Absolute Value in Excel

What is Absolute Value in Excel?

Microsoft Excel worksheet is a combination of numeric values and alphabets. The numeric value is either a positive value or a negative value. Sometimes the user needs to convert the negative to positive values for calculation purposes. Typing manually takes time and sometimes leads to errors. Excel provides a default function called ABSOLUTE to convert from negative to positive values. The ABSOLUTE function is called the ABS function, which returns the data without the sign. It converts the negative numbers to positive numbers, where the negative sign won’t appear while displaying the final result, and the positive numbers remain unchanged. This ABS function is used to return the absolute value of the number.

How to implement the ABS function?

Here is a simple example of converting the given data into a positive number using the ABS function. The steps to be followed are,

Step 1: Enter the data in the required cell, namely A1. The data entered here is a negative number.

Step 2: Select a new cell, B1, where the user wants to display the result. Enter the formula as =ABS (A1). Here A1 is referred to as the cell name.

Step 3: Press Enter. The result will be displayed in the cell where the ABS function returns the data as a positive number.

Absolute Value in Excel

From the above worksheet, the value present in cell A1 is changed from a negative number to a positive number.

 Example 1: How does the ABSOLUTE function work in Positive numbers?

In this example, the effect of the ABSOLUTE function in positive numbers is explained clearly. The steps to be followed are,

Step 1: Enter the data in the required cell, namely A1. The data entered here is a positive number.

Step 2: Select a new cell, B1, where the user wants to display the result. Enter the formula as =ABS (A1). Here A1 is referred to as the cell name.

Step 3: Press Enter. The result will be displayed in the cell where the ABS function returns data similar to the data present in cell A1.

Absolute Value in Excel

From the above worksheet, the ABS function returns a positive number similar to the data present in cell A1.

Example 2: How does the ABSOLUTE function work in Zero?

In this example, the effect of the ABSOLUTE function in zeros is explained clearly. The steps to be followed are,

Step 1: Enter the data in the required cell, namely A1. The data entered here is a zero.

Step 2: Select a new cell, B1, where the user wants to display the result. Enter the formula as =ABS (A1). Here A1 is referred to as the cell name.

Step 3: Press Enter. The result will be displayed in the cell where the ABS function returns data similar to the data present in cell A1.

Absolute Value in Excel

From the above worksheet, the result displayed in cell B1 is similar to that of cell A1 where the Absolute value of zero is zero.

The purpose of the ABS function in Excel has enormous. The ABS function is used to retrieve the result based on the given data.

Example 3: How to find the difference using the ABS function?

The ABS function is used in this example to find the difference between two data sets. The steps to be followed are,

Step 1: Enter the two data sets in the required cell range, A1:B5.

Step 2: Select a new cell, C1, where the user wants to display the result. Enter the formula as =ABS (A1-B1). Here A1 and B1 are referred to as cell names.

Step 3: Press Enter. The result will be displayed in the cell where the ABS function returns the result in cell C1, which is the difference between A1 and B1. Similarly to find the difference for the remaining cells, drag the formula toward the cell C5.

Absolute Value in Excel

From the above worksheet, the data difference is displayed in the column range A1:A5

Example 4: How to find the square root of the Negative number?

To find the square root of the negative number in the general method, the result will be as follows,

Absolute Value in Excel

From the above worksheet, the result will be displayed as NUMBER ERROR as the value present in the cell contains a negative value. Hence, the ABS function is implemented to find the SQRT of the negative number.

The steps to be followed are,

Step 1: Enter the data in the required cell range, A1. Here the data entered is a negative number.

Step 2: Select a new cell, B1, where the user wants to display the result. Enter the formula as =SQRT (ABS (A1)) to find the square root of the given number. Here A1 is referred to as the cell name.\

Absolute Value in Excel

The above worksheet shows the square root of the given number in cell B1.

Example 5: How to calculate the absolute error for the given data?

To calculate the absolute error for the given data, the steps to be followed are,

Step 1:  Step 1: Enter the three sets of data in the required cell range, A1:C6. The data entered are Month, Actual, and Forecast.

Step 2: Select a new cell, D1, where the user wants to display the result. Enter the formula as =ABS (B2 -C2). Here B2 and C2 are referred to as cell names.

Step 3: Press Enter. The result will be displayed in cell D2, which is the difference between B2 and C2. Similarly, to find the absolute error for the remaining cells, drag the formula toward cell D6.

Absolute Value in Excel

From the above worksheet, the ERROR is founded using the ABS function.

Example 6: Here is an example to find the forecast error based on SUMPRODUCT and ABS functions.

The steps to be followed are,

 Step 1: Enter the three data sets in the required cell range, A1:C6. The data entered are Month, Actual, and Forecast.

Step 2: Select a cell namely C7, where the user wants to display the result. Enter the formula as =SUMPRODUCT (ABS (B2:B6-C2:C6)). Here B2:B6 and C2:C6 are referred to as cell names.

Step 3: The result will be displayed in cell C7, called the absolute error.

Absolute Value in Excel

From the above worksheet, the ABS function returns the value of 20, where the result is similar to the previous concept.

Summary

From the above tutorial, the various functions and methods to calculate the absolute value are clearly explained.